Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Making a rolling forecast

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!

Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        v-chenwuz-msft
        Community Support
        4 years ago

        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.