cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
G0ggs
Helper I
Helper I

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

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 
2 ACCEPTED SOLUTIONS

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

View solution in original post

HarishKM
Super User
Super User

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


View solution in original post

7 REPLIES 7
HarishKM
Super User
Super User

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


View solution in original post

halfglassdarkly
Resolver I
Resolver I

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.

 

 

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? 

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

View solution in original post

Awesome thanks very much that worked!

Cheers

TomMartens
Super User
Super User

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Sorry it's sensitive info so can't upload 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.