Daily Running Total of Latest values per Group

[viewSonic123]

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

[FreemanZ]

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

[viewSonic123]

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

[viewSonic123]

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

Therefore the running total = 25

[FreemanZ · Accepted solution]

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:

[viewSonic123]

@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.

[viewSonic123]

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
                )
            )
        )
)

[FreemanZ]

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. 

[viewSonic123]

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

[viewSonic123]

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

Thank you again!

[viewSonic123]

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

[viewSonic123]

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