Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Impactful Individual
Impactful Individual

@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
Impactful Individual
Impactful Individual

@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
Resolver IV
Resolver IV

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

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.