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)

     

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