Dynamic measure value based on time slicer for running total

[aaronzheng]

I have a line graph that shows the runningtotal of applications for the past 31 days at the same hour each day. 

Each point on the graph is the runningtotal at the current hour, in this case it shows the runningtotal at 5:00 pm for the past month. 

The x axis is a calculated column called col_Current_Hour_Flag that returns a date/time if another column called current_hour_flag returns a 1 to indicate it is the current hour. 

col_Current_Hour_Flag = IF ( FLA[Current_Hour_Flag] = 1 , FLA[ApplicationHourEST] , BLANK() )

The y axis is my runningtotal measure.

RunningTotal =

CALCULATE(

SUM('FLA'[ApplicationCount])

,FILTER(

ALLSELECTED(FLA),

FLA[Time] <= MAX(FLA[Time])

&& FLA[Date] = MAX(FLA[Date])

)

)

However, I have a time slicer that has every hour. The default look for the visualization would be the current hour, but I would like the time slicer to be able to dynamically change the graph to represent the selected time. 

Please let me know if you need more information. Thanks.

[v-henryk-mstf]

Hi @aaronzheng ,

According to your description, I did the test with reference to the following process.

Measure =
VAR a =
    SELECTEDVALUE ( Table1[time] )
RETURN
    CALCULATE (
        SUM ( Table1[ApplicationCount] ),
        FILTER (
            ALL ( Table1 ),
            Table1[time] < a
                && Table1[data] = MAX ( 'Table1'[data] )
        )
    )

If the problem is still not resolved, can you provide test data (remove sensitive information) so that I can do further testing. Looking forward to your reply.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[aaronzheng]

Hi Henry, thanks for your reply. Heres the link for the sample data: https://docs.google.com/spreadsheets/d/1Ddmx0vcP7t4J1W3l4YgnxYAlXK7ZtRDJ/edit?usp=sharing&ouid=10423...

A revision to my question, instead of the line graph above I would like to apply the time slicer to this graph. 

This graph gives a runningtotal of applications for each day. I would like the time slicer to give the runningtotal at the selected time per day for each day. For instance if the time slicer is set at 5:00 pm, the graph above should match this: 

For example, the value for 11/4/2021 5:00 pm should be 24,745. The x axis for the Running Total (Last 31 Days) is ApplicationHourEST (not the hierachy, just the value) and the value is my runningtotal measure:

RunningTotal =

CALCULATE(

SUM('FLA'[ApplicationCount])

,FILTER(

ALLSELECTED(FLA),

FLA[Time] <= MAX(FLA[Time])

&& FLA[Date] = MAX(FLA[Date])

)

)

Currently, the graph incorrectly sums up the application count with a time selected:

1,923 runningtotal instead of 24,745.

Let me know if you need more information, and thanks again.

[v-henryk-mstf · Accepted solution]

Hi @aaronzheng ,

First I separate out the separate time field in the power query. Then I try the following formula to get the result that satisfies the time condition.

RunningTotal = 
VAR a =
    SELECTEDVALUE ( FLA[Time_] )
RETURN
    CALCULATE (
        SUM ( 'FLA'[ApplicationCount] ),
        FILTER ( ALL ( FLA ), FLA[Time_] <= a && FLA[Date] = MAX ( FLA[Date] ) )
    )

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[aaronzheng]

Thanks again for the help Henry, your solution worked for the time slicer of the total runningtotal. However, it doesn't work with my other filters, specifically state. I noticed in your runningtotal measure you used ALL, which might be the reason the runningtotal doesn't filter down. I am having trouble with this problem, any ideas?

For instance, the correct value at 11/10/21 20:00 for California (CA) would be 2,684. Thanks again.

[aaronzheng]

Hi Henry, thanks for your help. How exactly did you separate the time field in the power query? 

I have the datetimes here, but I would just like the 24 hours of the day like in your screenshot.