Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

YTD DAX

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 i...
Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    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's avatar
Anonymous
Not applicable
4 years ago

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.