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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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

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.

2 REPLIES 2
v-shex-msft
Community Support
Community Support

Hi @Anonymous,

 

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

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

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!

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.