Reply
Highlighted
Regular Visitor
Posts: 36
Registered: ‎04-06-2017

Cumulative values of most recent updates within time range of slicer

I have a file containing the monthly planned values (PV) for each project & expense.  This file can contain updates to these existing projections. So any given Month/Project/Expense (Key) can have orginal, monthly, and most recent updates. 

 

This column works to always provide the original values:

FirstDate = CALCULATE (
FIRSTDATE( 'PV Sample Data'[Update Date] ),
ALLEXCEPT( 'PV Sample Data', 'PV Sample Data'[Key] )
)
 
This column works to always provide the most recent values:
MostRecent = CALCULATE (
LASTDATE ( 'PV Sample Data'[Update Date] ),
ALLEXCEPT ( 'PV Sample Data', 'PV Sample Data'[Key] )
)
I can use it in a calculate formula and then in a cumulative: 
PV MostRecent Total = CALCULATE(sum('PV Sample Data'[PV Amount]),FILTER('PV Sample Data',
'PV Sample Data'[Update Date] = 'PV Sample Data'[MostRecent]))
 
 
I now need to create a formula that will respect the update date range so I can plot the cumulative values per quarterly update.
When selecting the quarter in a slicer, the cumulative value should be for all budget months. 
If there is no update in that quarter, it should just carry over the cumulative value, or it should provide the most recent value of that quater (if there were multiple updates to the PV for each 'key').
 
 
chart.PNG
 
 
Any Ideas? 
 
The above chart is wrong for it is accumulating all updated values and not just the most recent with the quarter period. 
Community Support Team
Posts: 1,380
Registered: ‎07-25-2018

Re: Cumulative values of most recent updates within time range of slicer

Hi @steph_io

 

It seems you may try to use ALLSELECTED Function. Below is the post for your reference. If it is not your case, please share the simplified data sample and expected output so that we could help further on it. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.

https://community.powerbi.com/t5/Desktop/Cumulative-with-a-slicer/td-p/469928

 

How to Get Your Question Answered Quickly

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.
Regular Visitor
Posts: 36
Registered: ‎04-06-2017

Re: Cumulative values of most recent updates within time range of slicer

The issue with All Selected is that it will only show amounts that have a updated value. I need to accumlate the amounts upto and including that quarter. Some periods have no udpates but the accumulated total still applies.

 

I have budget data per month spanning 2017 - 2024, but so far I only have 5 updates (2017-09, 2018-01, 2018-04, 2018-07, 2018-10). These updates can be to some past and/or future months.  When there is an update, I need to use that value within the updated range. 

 

So again it's not as of 'today's cumulative latest value. I need to plot it as it's been updated. 

sample data file