How to get running total to stop 45 days ago as there is no data after this point

[G0ggs]

Hello I have a running total quick measure which is calculating a running total however there is no data after 45 days ago but the running total is still calculating a flat line up to today. 

How do I stop the running total 45 days ago instead of running to today please?

SORRate running total in Date 2 =

CALCULATE(

    SUM('vwP6ReportAll'[SORRate]),

    FILTER(

        ALLSELECTED('Date'[Date]),

        ISONORAFTER('Date'[Date], MAX('Date'[Date]), DESC)

    )

)

Thanks,

Gordon 

[halfglassdarkly · Accepted solution]

Try:

= IF(SELECTEDVALUE('Date'[Date])<=TODAY()-45,

   CALCULATE(

    SUM('vwP6ReportLatest'[Allocation Rate post RWO]),

    FILTER(

        ALLSELECTED('Date'[Date]),

        ISONORAFTER('Date'[Date], MAX('Date'[Date]), DESC)

    )

  )

)

[HarishKM · Accepted solution]

@G0ggs  Hey ,
You can use this one 

SORRate running total in Date 2 =

if(SUM('vwP6ReportAll'[SORRate])<>0,CALCULATE(

    SUM('vwP6ReportAll'[SORRate]),

    FILTER(

        ALLSELECTED('Date'[Date]),

        ISONORAFTER('Date'[Date], MAX('Date'[Date]), DESC)

    )

),0
)

Here i am doing if sale is other than 0 then only calculate otherwise show 0 as output.

[halfglassdarkly]

You can just wrap your CALCULATE statement inside an IF statement that checks if max('Date'[Date] is <= [stop date]

return your calculation if true, with no false option set.

[G0ggs]

Em I am struggling with how to write that formula? The stop date is always changing as it is just 45 days in the past. 

Allocation Rate post RWO running total in Date =

IF

'Date' = Date - 45

Blank(),

CALCULATE(

    SUM('vwP6ReportLatest'[Allocation Rate post RWO]),

    FILTER(

        ALLSELECTED('Date'[Date]),

        ISONORAFTER('Date'[Date], MAX('Date'[Date]), DESC)

    )

)

Something like that? 

[halfglassdarkly · Accepted solution]

Try:

= IF(SELECTEDVALUE('Date'[Date])<=TODAY()-45,

   CALCULATE(

    SUM('vwP6ReportLatest'[Allocation Rate post RWO]),

    FILTER(

        ALLSELECTED('Date'[Date]),

        ISONORAFTER('Date'[Date], MAX('Date'[Date]), DESC)

    )

  )

)

[G0ggs]

Awesome thanks very much that worked!

Cheers

[TomMartens]

Hey @G0ggs ,

please create a pbix file that contains sample data but still reflects your data model (tables, relationships, columns, calculated columns, and measures). Upload the file to onedrive or dropbox and share the link. If you are using Excel to create the sample data instead of the manual input method share the xlsx as well.

Regards,

Tom

[G0ggs]

Sorry it's sensitive info so can't upload