DAX YTD function does not work for measure

[DaniZ1]

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 until this year end (2022).

I uploaded a sample data set, recreating the issue:
What I need:

- is a table with actual data until current month, budget data for all months (that's the easy part as its only sum of my data columns)
- a forecast for each month, based on following rules:

1. a) if the month is already over, use the Actual
   b) if its a future month, use the budget
   c) if its the current month (as of today its May): use the greater of the two

Expected figures:

what I did:
1) create a measure to calculate Total Actual and Total Budget:

Total Actual = sum('Test Dataset'[Actual])

Total Budget = sum('Test Dataset'[Budget])

2) create a measure to calculate each month value, according the the rules above:

Forecast Month =

// For past periods use Actual, for future period use Plan, for current month take the higher amount of plan / actual

Var is_actual_exceeds_budget =

'Test Dataset'[Total Actual]> [Total Budget]

Var is_current_month_year =

(year( TODAY()-1) = year(SELECTEDVALUE ('Test Dataset'[Date]) ))&&

(month( TODAY()-1) = month(SELECTEDVALUE('Test Dataset'[Date])) )

Var is_future_month =

SELECTEDVALUE('Test Dataset'[Date]) > EOMONTH(today()- 1,0)

RETURN

if(is_current_month_year = TRUE() , if ( is_actual_exceeds_budget= TRUE(), [Total Actual], [Total Budget]),if(is_future_month=TRUE(),[Total Budget], 'Test Dataset'[Total Actual])

)

3) Create a Ytd measure to get the accumulated forecast over the year:

YTD Forecast = TOTALYTD([Forecast Month], 'calendar'[Date])

What I get in Power BI Desktop:

As shown in the screenshot, the month forecast value is  correct, according to the rules (actual for the past, budget for the future, and the budget in May, as its greater than the Actual in May).

But the Ytd for the accumulated forecast isn't correct, apparently, it only calculates the YtD Actual values.

I also attached the Power BI file with the sample dataset.
https://drive.google.com/file/d/1TDN4_qa-FbpO3cPhT4CCoWj92FYgca0m/view?usp=sharing 

I would very much appreciate your help!  Thank you!

Best

Daniela

[v-yalanwu-msft · Accepted solution]

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]

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.

[DaniZ1]

Hi @v-yalanwu-msft,

thank you so much for this, looks very much like this is the solution to my issue. I have also tested the measure on my original data and it works perfectly.

Big thanks!!
Daniela

[DaniZ1]

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]

@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]

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

[DataInsights]

@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: