cancel
Showing results for
Did you mean:
Highlighted
Regular Visitor

## 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
Community Support Team

## 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

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

For learning resources/Release notes, please visit: | |
Regular Visitor

## 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!

Announcements

#### Challenge: Can You Solve These?

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

#### Community News & Announcements

Get your latest community news and announcements.

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 61 members 1,330 guests
Recent signins: