Running Total Formula - Stop calculation at the latest data available

[Julio-YYC]

Hi there,

I have a measure cauculating my running total for hours spent:

Running Total Actual hr =

CALCULATE(

    SUM('4. Actual Manpower Forecast'[Actual hr]),

    FILTER(

        ALLSELECTED('Report/Week/Discipline'[Week Date]),

        ISONORAFTER('Report/Week/Discipline'[Week Date], MAX('Report/Week/Discipline'[Week Date]), DESC)

    )

)

However, I'd like to stop my calculation on Jan29th... as this is the latestdate with actuals hours. From Feb 05th onwards, I'd like to see blank values..

any idea? 

thansk! 

[v-zhangti · Accepted solution]

Hi, @Julio-YYC 

You can try the following methods.

Measure:

Running = 
CALCULATE ( SUM ( 'Table'[Actual hr] ),
    FILTER ( ALL ( 'Table' ), [Week Date] <= SELECTEDVALUE ( 'Table'[Week Date] ) )
)

Result = 
 Var _maxtotal=MAXX(ALL('Table'),[Running])
 Var _lastdate=CALCULATE(MIN('Table'[Week Date]),FILTER(ALL('Table'),[Running]=_maxtotal))
 Return
 IF(SELECTEDVALUE('Table'[Week Date])>_lastdate,BLANK(),[Running])

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

[Julio-YYC]

That worked perfect! Thanks v-zhangti ! 

[Julio-YYC]

That worked perfect! Thanks v-zhangti !

[AndreGiovannini]

Hi Julio,

I believe you could you could use a DATESBETWEEN as a filter, using its in date as date table and max date as today.

CALCULATE(

    SUM('4. Actual Manpower Forecast'[Actual hr]),

    FILTER(

        ALLSELECTED('Report/Week/Discipline'[Week Date]),

        ISONORAFTER('Report/Week/Discipline'[Week Date], MAX('Report/Week/Discipline'[Week Date]), DESC),
        DATESBETWEEN( 'date table and column', MIN ('date table and column'), today())

    )

Related: DATESBETWEEN function (DAX) - DAX | Microsoft Learn

[Julio-YYC]

Hi Andre, when I use your measure, I lost the cummulative calculation...weird (look at the last column): 

Test Measure =

CALCULATE(

    SUM('4. Actual Manpower Forecast'[Actual hr]),

    FILTER(

        ALLSELECTED('Report/Week/Discipline'[Week Date]),

        ISONORAFTER('Report/Week/Discipline'[Week Date], MAX('Report/Week/Discipline'[Week Date]), DESC)

    ), DATESBETWEEN('Report/Week/Discipline'[Week Date],MIN('Report/Week/Discipline'[Week Date]), TODAY())

)

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.