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 is till APRIL 2022 then Query should return

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

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)

     

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    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

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

       

  • johncolley's avatar
    johncolley
    Solution Sage
    4 years ago

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

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

  • 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.