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.

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