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,

 

Try this solution. I created a Forecast calculated table to simplify the calculation.

 

1. Change the calendar table to CALENDARAUTO (or some equivalent) to ensure that there are no gaps in the dates. Mark the table as a date table.

 

2. Create calculated table:

 

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

 

3. Create relationship between the calendar and Forecast tables (Date column):

 

 

4. Create measure:

 

YTD Forecast = CALCULATE ( SUM ( Forecast[Forecast] ), DATESYTD ('calendar'[Date] ) )

 

5. Result: