YTD DAX

[Anonymous]

I am looking for a YTD measure WHICH

will give me percent growth rate change for YTD the data available for latest month as compared to previous year.

For example IF the latest data available is till APRIL 2022 then Query should return

[Sum(January 2022-April 2022 ) - Sum (January 2021 - April 2021)] / Sum (January 2021 - April 2021)

[v-rzhou-msft]

Hi @Anonymous ,

I think you can try my code to create a measure. It is dynamic.

I create a related Dimdate table.

DimDate = 
CALENDARAUTO()

The data in my data table is from 2021/01/01 to 2022/04/30.

Measure:

YTD DIFF PERCENTAGE = 
VAR _THISYEAR_ENDDATE =  CALCULATE( MAX('Table'[Date]),ALL(DimDate))
VAR _THISYEAR_STARTDATE = DATE(YEAR(_THISYEAR_ENDDATE),1,1)
VAR _LASTYEAR_ENDDATE = EOMONTH(_THISYEAR_ENDDATE,-12)
VAR _LASTYEAR_STARTDATE = DATE(YEAR(_THISYEAR_ENDDATE)-1,1,1)
VAR _CURRENT_YTD = CALCULATE(SUM('Table'[Value]),FILTER(ALL(DimDate),AND( DimDate[Date]>=_THISYEAR_STARTDATE,DimDate[Date]<=_THISYEAR_ENDDATE)))
VAR _PREVIOUS_YTD = CALCULATE(SUM('Table'[Value]),FILTER(ALL(DimDate),AND( DimDate[Date]>=_LASTYEAR_STARTDATE,DimDate[Date]<=_LASTYEAR_ENDDATE)))
RETURN
DIVIDE(_CURRENT_YTD - _PREVIOUS_YTD,_PREVIOUS_YTD)

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[johncolley]

Hi,

There may be a more elegant solution but I was able to produce the end result with the following:

YTDValues = 

VAR month = 4

return

CALCULATE([YTD Value],'Calendar'[Month] <= month)

**Please note i set Var month = 4 here for April with the dummy data. You can replace with Var month = Month(Today()) for a dynamic solution.

%changemeasure = 

Var cytd = CALCULATE([YTDValues],'Calendar'[Year] = 2022)

Var pytd = CALCULATE([YTDValues],'Calendar'[Year] = 2021)

return

(cytd - pytd) / pytd * 100

[Anonymous]

We already have this hardcoded. I am looking for dynamic query

[amitchandak · Accepted solution]

@Anonymous , Try measure like this using time intelligence

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

or

YTD QTY forced=
var _max = eomonth(today(),-1)
return
if(max('Date'[Date])<=_max, calculate(Sum('order'[Qty]),DATESYTD('Date'[Date])), blank())
//or
//calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),filter('Date','Date'[Date]<=_max))
//calculate(TOTALYTD(Sum('order'[Qty]),'Date'[Date]),filter('Date','Date'[Date]<=_max))

LYTD QTY forced=

var _today = eomonth(today(),-1)
var _max = date(year(_today )-1,month(_today ),day(_today ))
return
if(max('Date'[Date])<=_max, CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max), blank())
//OR
//CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)
//TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)

[Anonymous]

this gives the required number but still i am unable to implement this