cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
wi11iamr Regular Visitor
Regular Visitor

Running Sum: Use alternate date in report than date used to calculate running sum

Hi there,

 

I have an InventoryForecast table for which I successfully calculate a Running Total/Cumulative measure by using the EndDate for each forecasted item. Code as follows:

Forecast Cost Cumulative:=
CALCULATE (
   SUMX('Inventory Planning', 'Inventory Planning'[Qty] * 'Inventory Planning'[Cost]),
    FILTER (
        ALL (' Inventory Planning'[EndDate] ),
        'Inventory Planning'[EndDate] <= MAX ('Inventory Planning'[EndDate] )
    )
)

I then have a DateKey ('dimCalendar') for which my 'Inventory Planning' table has a relationship for EndDate to the  dimCalendar[Date].

 

While I understand that the cumulative measure uses EndDate from it's same table to perform the running sum, if I wish to enrich my report by instead using columns from my dimCalendar (such as Month Name, Fiscal periods etc), the measure does not produce a running total and merely reflects the individual value for that month, as per the below screenshot.

 

Also, with an additional date field in my report, end users will continue to be fconfused and will very likely not use the "corect" EndDate field and when using the DateKey fields will end up with wrong results.

 

The question then is whether there is any means by which I can effectively hide the EndDate from the users so they select only from the DateKey fields, while still preserving the inegrity of my runnign sum calculation?

Capture.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Running Sum: Use alternate date in report than date used to calculate running sum

Hi,

 

Drag the Date from the Calendar Table and write the following measure:

 

=CALCULATE(SUMX('Inventory Planning','Inventory Planning'[Qty]*'Inventory Planning'[Cost]),DATESBETWEEN('dimCalendar'[Date],MINX(ALL('dimCalendar'),'dimCalendar'[Date]),MIN('dimCalendar'[Date])))

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

5 REPLIES 5
v-cherch-msft Super Contributor
Super Contributor

Re: Running Sum: Use alternate date in report than date used to calculate running sum

Hi @wi11iamr

 

You may try below measure:

Forecast Cost Cumulative:=
CALCULATE (
   SUMX('Inventory Planning', 'Inventory Planning'[Qty] * 'Inventory Planning'[Cost]),
    FILTER (
        ALL (' Inventory Planning' ),
        'Inventory Planning'[EndDate] <= MAX ('Inventory Planning'[EndDate] )
    )
)

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User
Super User

Re: Running Sum: Use alternate date in report than date used to calculate running sum

Hi,

 

Drag the Date from the Calendar Table and write the following measure:

 

=CALCULATE(SUMX('Inventory Planning','Inventory Planning'[Qty]*'Inventory Planning'[Cost]),DATESBETWEEN('dimCalendar'[Date],MINX(ALL('dimCalendar'),'dimCalendar'[Date]),MIN('dimCalendar'[Date])))

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

wi11iamr Regular Visitor
Regular Visitor

Re: Running Sum: Use alternate date in report than date used to calculate running sum

Ah, beautiful, thanks @v-cherch-msft! While I see what you've done by removing the [EndDate] from the filter context, I admittedly don't fully comprehend the logic in doing so. If you have a minute, would you mind clarifying so I may better understand for future code?

 

That said, while the Running Sum now works well at an aggregated level, when I slice by another dimension, such as vendor. then each vendor has the exact same running total sum as the overall total. Any thoughts on how to remedy this?

wi11iamr Regular Visitor
Regular Visitor

Re: Running Sum: Use alternate date in report than date used to calculate running sum

Thanks @Ashish_Mathur, this does in deed work well and also retains the relatiosnship with my Vendor table for further slicing.

Super User
Super User

Re: Running Sum: Use alternate date in report than date used to calculate running sum

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 399 members 3,342 guests
Please welcome our newest community members: