Forum Discussion

DaniZ1's avatar
DaniZ1
Regular Visitor
4 years ago
Solved

DAX YTD function does not work for measure

Dear Power BI experts,   I am attempting to create a budget report with actual and budget figures on a monthly basis for 2022 and want to create a forecast measure, to forecast the remaining months...
Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    Hi, DaniZ1 ;

    You only could change the measure to it.

    YTD Forecast = SUMX(FILTER(ALL('calendar'),EOMONTH([Date],0)<=EOMONTH(MAX('calendar'[Date]),0)),[Forecast Month])

    The final show:


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

DaniZ1's avatar
DaniZ1
Regular Visitor
4 years ago

Hi DataInsights , thank you very much for your help on this!

I followed your instructions and get the correct results. The issue I have with this approach, is that I cannot apply any filters on additional fields I have in the data, like "product" in my sample data, as this is not part of the new calculated table.
Would you have any thoughts on this, too?

Big thanks!

  • DataInsights's avatar
    DataInsights
    Icon for Super User rankSuper User
    4 years ago

    DaniZ1,

     

    The limitation of the calculated table approach is that it doesn't recognize user filters (e.g., slicers) in the calculation. Try the calculated table below that includes product and see if that meets the requirements. You'll need to create a DimProduct table in order to filter both fact tables for a product.

     

    Forecast = 
ADDCOLUMNS (
    SUMMARIZE ( 'Test Dataset', calendar[Year], calendar[Month No], 'Test Dataset'[Product] ),
    "Date", DATE ( calendar[Year], calendar[Month No], 1 ),
    "Forecast", [Forecast Month]
)

     

    • DaniZ1's avatar
      DaniZ1
      Regular Visitor
      4 years ago

      Hi DataInsights ,

       

      thanks again so much for your efforts.

       

      Yes, you are right, this is a big issue, as my original data is much more complex and I need a bunch of user filters, that affect both fact tables.

      You also dont see any other way to get my original measure working without the additional calculated table?

      Thank you very much!
      Best

      Daniela