YTD figures displaying in Y-Q-M hierarchy for future months

0
votes

I have a simple profit value that I'd like to calculate as YTD but only show the figures up to the current month.

i.e. With just YTD it looks like this:

Normal

But I'd like it to look like this:

Target

I am currently using the following DAX formula to achieve this:

Profit YTD:=IF (
MAX ( 'Date'[Date_Key] ) <= TODAY (),
( TOTALYTD ( [Profit], 'Date'[Date_Key] ) ),
BLANK ()
)

This works great until I use the date hierarchy (Year-Quarter-Month) as then the data for the current year is not displayed:

Result

I know why this happens, but I don't know how to resolve this in DAX. Any help greatly appreciated.

1
powerbidax

1 Answers

0
votes

I found a solution on sqlbi.com:

hiding-future-dates-for-calculations-in-dax

From this I created the following calculated column in the Date table:

DateWithProfit= 'Date'[Date] <= NOW()

I then added the following measure:

Profit YTD:=
CALCULATE (
[Profit],
CALCULATETABLE ( DATESYTD ( 'Date'[Date] ), 'Date'[DateWithProfit] = TRUE )
)

Works perfectly.