Cumulative SUM based on different dates in two different measures?

[williamadams12]

I'm struggling to create a single measure cumulative sum that's based on an aggregate of two different measures each with a different set of dates. 

Has anyone ever tried this before?

[v-xicai]

Hi   @williamadams12 ,

Does that make sense? If so, kindly mark the proper reply as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

Best regards

Amy

[williamadams12]

Apologies for the delayed response, I was out for a few weeks with a broken clavicle and broken arm, so I'm just getting back to typing reasonably well 🙂 

This looks helpful so far, but I'm still having trouble with the date filters per each measure. 

Essentially I have an aggregated total by month for two different measures that total out to an entire year. 

Actuals= Jan -> June

Projections = July ->Dec 

I want to add these two measures together for a cumulative sum I need to add to an area chart comparison.

[v-xicai]

Hi @williamadams12 ,

You may create measure like DAX below.

Cumulative SUM=

Var _MinDate= [Measure StartDate]

Var _MaxDate= [Measure EndDate]

Return

CALCULATE(SUM(Table1[Value]),FILTER(ALLSELECTED(Table1), Table1[Date] >=_MinDate&& Table1[Date] <=_MaxDate ))

If I misunderstood it, could you please share your sample data and desired output screenshots for further analysis? You can also upload sample pbix to OneDrive and post the link here. Do mask sensitive data before uploading.

Please read this post to get your answer quickly: How to Get Your Question Answered Quickly.

Best Regards,

Amy 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@williamadams12 , you can join both dates with same date table use userelation .

example

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=maxx(date,date[date])),userelation(Date[date],Sales[order Date]))
+ CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=maxx(date,date[date])),userelation(Date[date],Sales[delivery Date]))

Refer example here to https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-1-Time-Intell...

[kentyler]

If you use VAR statements it should be straightforward

Grand Total = 
VAR total1 = [total1 measure]

VAR total2 = [total2 measure]
RETURN total1 + total2

This assumes all the work is being done in the sub measures, including the date filtering for different dates. Since you're filtering for different date ranges you will probably need to use CALCULATE and change the filter context for each of the sub measures. Let us know if you need help with the DAX for the sub measures... please post some small sample data.