Making a rolling forecast

[Anonymous]

Hi all,

I'm trying to make a rolling forecast in which you select a month and it gives you an overview of the revenue until that month and the budget until the end of the year. Right now i got this:

For the rolling forecast in this example i took the sum of the revenue in month 1 to 6 and for the budget i took the total of the whole year minus the budget in month 1 to 6. But I would like to make a matrix with the month as columns. Right now the slicer makes that impossible as it would hide the months 7 through 12 in the matrix. Is there a way to implement this?

Thanks!

[amitchandak]

@Anonymous , with help from date table

till date

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))

Complete year

This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))

or

This Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))

[Anonymous]

Thanks for your response! These measures won't show month 7 till 12 in a matrix still though right? The slicer makes it that only month 1 to 6 are shown.

[v-chenwuz-msft]

Hi @Anonymous ,

To remove the slicer affecting you can use eidt interaction.

Or you can use the some measures with REMOVEFILTERS() to return values without affecting by slicer.

Best Regards

Community Support Team _ chenwu zhu

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