topic Re: Daily Running Total of Latest values per Group in DAX Commands and Tips

Daily Running Total of Latest values per Group

viewSonic123 — Sun, 11 Dec 2022 04:37:07 GMT

Hi,

I'm trying to get a daily running total of the values of 1 group. The data received is not daily and changes are only shown if the ID gets updated. I need to get a running total per day of the latest value by ID. Help would be greatly appreciated!

Any idea of how to keep this as a measure? I also have a slicer that allows users to choose the max date (ex. max date 1/6 would not have 1/7 data present)

edit: I'm trying to insert a pbix but says that it is not supported...

Sample data I get:

ID	Value	Date
1	1	1/1/2022
2	2	1/3/2022
1	6	1/4/2022
2	4	1/4/2022
3	2	1/5/2022
1	15	1/7/2022
2	0	1/6/2022
3	10	1/6/2022

running total: what I want

Date	Running total
1/1/2022	1
1/2/2022	1
1/3/2022	3
1/4/2022	10
1/5/2022	12
1/6/2022	16
1/7/2022	25

Re: Daily Running Total of Latest values per Group

viewSonic123 — Sun, 11 Dec 2022 03:58:45 GMT

Date	Running Total
1/1/22	1
1/2/22	1
1/3/22	3
1/4/22	10
1/5/22	12
1/6/22	16
1/7/22	25

Re: Daily Running Total of Latest values per Group

viewSonic123 — Sun, 11 Dec 2022 03:59:24 GMT

Running total	Date
1	1/1/2022
1	1/2/2022
3	1/3/2022
10	1/4/2022
12	1/5/2022
16	1/6/2022
25	1/7/2022

Result expected

Re: Daily Running Total of Latest values per Group

FreemanZ — Sun, 11 Dec 2022 04:34:41 GMT

For 1/7/2022, how do you get 25?

Re: Daily Running Total of Latest values per Group

viewSonic123 — Sun, 11 Dec 2022 04:39:53 GMT

Sorry, I accidentally changed the value for 1, 15, 1/7/2022 (correct) to 1, 8, 1/7/2022 (incorrect)

I have changed the main question with the correct data.

1/7/22 would be 25 as the most recent for 1 = 15, 2 = 0, 3 = 10

Re: Daily Running Total of Latest values per Group

viewSonic123 — Sun, 11 Dec 2022 05:22:57 GMT

For 1/7 ID's 1,2,3 have a most recent value of 15, 0, 10, respectively

Therefore the running total = 25

Re: Daily Running Total of Latest values per Group

FreemanZ — Sun, 11 Dec 2022 06:16:32 GMT

hi viewSonic123

you would need to

1) create a Date Table with this code

DateTable = CALENDAR(MIN('FactTable'[Date]), MAX('FactTable'[Date]))

2) connect two Date columns

3) create a measure with this code:

LatestSum = VAR CurrentDate = MAX(DateTable[Date]) RETURN SUMX( ALL(FactTable[ID]), VAR CurrentID = FactTable[ID] VAR MaxDate = CALCULATE( MAX(FactTable[Date]), FILTER( ALL(FactTable), FactTable[Date] <= CurrentDate && FactTable[ID] = CurrentID ) ) RETURN CALCULATE( MAX(FactTable[Value]), FILTER( ALL(FactTable), FactTable[Date] = MaxDate && FactTable[ID] = CurrentID ) ) )

4) then plot with the DateTable[Date] and the measure above.

I tried and it worked like this:

Re: Daily Running Total of Latest values per Group

viewSonic123 — Sun, 11 Dec 2022 17:31:42 GMT

Thank you so much FreemanZ! The measure you provided got me what I wanted! I need to get better at dax.

Thank you again!

Re: Daily Running Total of Latest values per Group

viewSonic123 — Mon, 12 Dec 2022 00:43:06 GMT

FreemanZ How would I be able to add multiple 'All Page' filters onto this measure? Is that possible?

Ex. I have a filter in the slicer pane and would like for the measure to filter on that filter.

Re: Daily Running Total of Latest values per Group

viewSonic123 — Mon, 12 Dec 2022 01:11:00 GMT

I am able to get what I wanted in the above reply:

Is this what others would do? ( I added the KEEPFILTERS() function )

LatestSum = VAR CurrentDate = MAX(DateTable[Date]) RETURN SUMX( ALL(FactTable[ID]), VAR CurrentID = FactTable[ID] VAR MaxDate = CALCULATE( MAX(FactTable[Date]), FILTER( ALL(FactTable), FactTable[Date] <= CurrentDate && FactTable[ID] = CurrentID ) ) RETURN CALCULATE( MAX(FactTable[Value]), KEEPFILTERS( FILTER( ALL(FactTable), FactTable[Date] = MaxDate && FactTable[ID] = CurrentID ) ) ) )

Re: Daily Running Total of Latest values per Group

FreemanZ — Mon, 12 Dec 2022 02:06:02 GMT

congratulations. KEEPFILTER is suspposed to work in any filter argument of CALCULATE, no matter how complicated it is. Your scenario is already very specific and complicated, it is hard to say what others would do.

Re: Daily Running Total of Latest values per Group

viewSonic123 — Mon, 12 Dec 2022 02:27:31 GMT

Thank you again FreemanZ for the response! You have helped me a great deal. Thanks!