Add Value from previous day

[Simon_Blackburn]

I am creating an app to track expenses. 

I have a virtual column which adds the days expenses and displays them in a column called [Daily Total]. 

I want to create another virtual column called [Running Total] that will add todays [Daily Total] to yesterdays. 

Because this will be a Monday to Friday thing, when it comes to Monday, I don’t want it to just add to the previous row because that will then add the Friday from the previous week running total. It needs to restart again on Monday. 

What expression will I need to use for this please. 

[lynchk21 · Accepted solution]

It seems like you want a 'Weekly Total' column in addition to your 'Daily Total'. Maybe try something like the following:

SUM(
SELECT(
TableName[DailyTotalColumn],
AND(
[DateColumn] >= ([_THISROW].[DateColumn] - WEEKDAY([_THISROW].[DateColumn]) + 1),
[DateColumn] <= ([_THISROW].[DateColumn] + (5 - WEEKDAY([_THISROW].[DateColumn])))
)
)
)

[Simon_Blackburn]

That works perfectly, thank you. I’d love to be able to understand what’s going on in that expression. 

[lynchk21]

If you imagine that the [DateColumn] = 1/1/2024 which is a Monday (WEEKDAY = 1) then for the first AND condition:

• [DateColumn] >= ([_THISROW].[DateColumn] - WEEKDAY([_THISROW].[DateColumn]) + 1)
• [1/1/2024] >= ([_THISROW].[1/1/2024] - WEEKDAY([_THISROW].[1/1/2024]) + 1)
  • [1/1/2024]>=[1/1/2024] - 1 + 1
  • [1/1/2024]>=[1/1/2024] + 0
  • [1/1/2024]>=[1/1/2024] 
  • TRUE

For the second AND condition

• [DateColumn] <= ([_THISROW].[DateColumn] + (5 - WEEKDAY([_THISROW].[DateColumn]))
• [1/1/2024] <= ([_THISROW].[1/1/2024] + (5 - WEEKDAY([_THISROW].[1/1/2024])
  
  • [1/1/2024] <=[1/1/2024] + 5-1
  • [1/1/2024] <=[1/1/2024] + 4
  • [1/1/2024] <=[1/5/2024] 
  • TRUE

For any [DateColumn] value, the expression is returning a list of values that meet the two conditions above, and as you can see the condition is limiting the date range to a Monday - Friday, in this case 1/1/2024-1/5/2024.

If you choose another date from within the 1/1/2024-1/5/2024 week, you will see that is still meets the AND requirements:

First AND condition:

• [DateColumn] >= ([_THISROW].[DateColumn] - WEEKDAY([_THISROW].[DateColumn]) + 1)
• [1/4/2024] >= ([_THISROW].[1/4/2024] - WEEKDAY([_THISROW].[1/4/2024]) + 1)
  • [1/4/2024]>=[1/4/2024] - 4 + 1
  • [1/4/2024]>=[1/4/2024] -3
  • [1/4/2024]>=[1/1/2024] 
  • TRUE

For the second AND condition

• [DateColumn] <= ([_THISROW].[DateColumn] + (5 - WEEKDAY([_THISROW].[DateColumn]))
• [1/4/2024] <= ([_THISROW].[1/4/2024] + (5 - WEEKDAY([_THISROW].[1/4/2024])
  
  • [1/4/2024] <=[1/4/2024] + 5-4
  • [1/4/2024] <=[1/4/2024] + 1
  • [1/4/2024] <=[1/5/2024] 
  • TRUE