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.
