Cumulative Value not stopping at last refreshed time

[Anonymous]

Hi Members,

I am facing some challenge in my Power BI Report as I have created one Dax to aggregate the running jobs on the bases of time and it should stop at last refreshed time however it is not stopping at that point.

Here is the DAX that I have created for the same :

Finished Today running total in Rounded Time 2 =
CALCULATE(
[Finished Today],
FILTER(
ALL('Query1'[Rounded Time]),
ISONORAFTER('Query1'[Rounded Time], MAX('Query1'[Rounded Time]), DESC)
)
)

This is the visual that I am geeting.

Please suggest some steps to resolve the same, It will be really helpful.

Thankyou in advance.

[Anonymous]

HI @Anonymous,

I found you are used the ALL function in the expression, they may affect the aggregate function usage and get the last date instead of the current row context. I'd like to suggest you try to use the following measure formulas if helps:

Finished Today running total in Rounded Time 2 =
CALCULATE (
    [Finished Today],
    FILTER (
        ALLSELECTED ( 'Query1' ),
        'Query1'[Rounded Time] <= MAX ( 'Query1'[Rounded Time] )
    ),
    VALUES ( 'Query1'[Rounded Time] )
)

Regards,

Xiaoxin Sheng

[Anonymous]

Thank you Sheng for your time and suggestion.

I used the same measure that you suggested, It is not aggregating the value time wise.

Sharing the image below for your refrence.

Rounded 3 column 

This suggested measure I used:

Finished Today running total in Rounded Time 3 =
CALCULATE (
[Finished Today],
FILTER (
ALLSELECTED ( 'Query1' ),
'Query1'[Rounded Time] <= MAX ( 'Query1'[Rounded Time] )
),
VALUES ( 'Query1'[Rounded Time] )
)

I am trying to get values adding to my report each time when it get finished and should stop at last refreshed time.

As I am comparing my todays jobs with average of last 7 days jobs that time the sum of my todays jobs is contionously showing till last. 

Please suggest I am can any more changes in suggested measure.

[Anonymous · Accepted solution]

HI @Anonymous,

Did you mean the refresh time is not a static value and it dynamic change based on the current datetime? If that is the case, you can use the current date value to get the previous date, then use this defined date range to calculation rolling results.

Finished Today running total in Rounded Time 3 =
VAR currDate =
    MAX ( 'Query1'[Rounded Time] )
RETURN
    CALCULATE (
        [Finished Today],
        FILTER (
            ALLSELECTED ( 'Query1' ),
            'Query1'[Rounded Time] >= currDate - 7
                && 'Query1'[Rounded Time] <= currDate
        )
    )

Regards,

Xiaoxin Sheng