Reply
Regular Visitor
Posts: 31
Registered: ‎10-06-2016

DAX Calculate Total Amount for next 4-6 Months rolling filter

[ Edited ]

Hello,

 

I've tried to figure this out and I can't get an example anywhere.  I'm pretty sure I'm doing this very inefficiently.

 

I want to calculate the future revenue for the next 4-6 months and display it in a stacked chart.  I have been able to calculate the revenue in a way I'm sure isn't really recommended but basically I have made two tables with the dates I wanted filtered in power query before bringing it into the model.  Then I've created two measures:

 

Next 3 Mth £ = CALCULATE([Quote Value £],DATESBETWEEN('Opportunity 3 Months'[estimatedclosedate],FIRSTDATE('Opportunity 3 Months'[estimatedclosedate]),LASTDATE('Opportunity 3 Months'[estimatedclosedate])))

 

Next 6 Mth £ = CALCULATE([Quote Value £],DATESBETWEEN('Date Est Close Date'[Date],firstdate('Opportunity 6 Months'[estimatedclosedate]),LASTDATE('Opportunity 6 Months'[estimatedclosedate])))

 

Then Next 4-6 Mth £ = [Next 6 Mth £]-[Next 3 Mth £].  It's not very elegant.  Clearly when I put this in a graph I get the right total then when I add dates it looks weird.

 

I have a Opportunity table with all the dates for next 18 months but I couldn't work out how to make the rolling date measures from that with the calculated filters so I made some extra tables filtered to the dates I wanted and then created the measures with those instead.  I'm sure I shouldn't be creating mutiple tables as it will take up memory.

 

Can anyone help at all?  Many thanks.

Super Contributor
Posts: 1,259
Registered: ‎08-14-2016

Re: DAX Calculate Total Amount for next 4-6 Months rolling filter

Hi @MunroA7,

 

It will be help if you share some sample file and screenshots.

 

Regards,

Xiaoxin Sheng

Regular Visitor
Posts: 31
Registered: ‎10-06-2016

Re: DAX Calculate Total Amount for next 4-6 Months rolling filter

Not to worry, I did an Anti-Left join on my two tables and will calculated from the dates in that.  It's not very elegent but it will have to do!