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.

v-yalanwu-msft's avatar
v-yalanwu-msft
Icon for Community Support rankCommunity Support
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

Great solution, v-yalanwu-msft. It's simple and dynamic. 🙂

 

I recommend using ALLSELECTED instead of ALL since the dataset will likely contain multiple years. Also, EOMONTH isn't necessary.

 

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

 

To illustrate the point, I added data for 2021:

 

 

Using ALL includes 2021 and 2022, even though only 2022 is selected in the slicer. This is due to ALL removing all filters from the calendar table.

 

 

Using ALLSELECTED includes only 2022, since only 2022 is selected in the slicer. This is due to ALLSELECTED removing all filters from the calendar table that come from within the visual (matrix rows and columns), but keeping external filters such as slicers.