Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

YTD % DAX calculation.

Hi  - I hope someone can help me. I am lookign to calculate YTD%, (which is basically supposed to be -- (CurrentQ - prior 12/31) / prior 12/31) ).  I have used quick measure to calculate QoQ %, whic...
  • Tutu_in_YYC's avatar
    Tutu_in_YYC
    4 years ago

    My mistake, previous calculations will not work in your model.
    try this:

    YoY% for YTD = 

    IF(
        ISFILTERED('Data2'[Date]),
        ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
        VAR __PREV_YEAR = CALCULATE(TOTALYTD(SUM('Data2'[$]), 'Data2'[Date].[Date]), DATEADD('Data2'[Date].[Date], -1, YEAR))
        RETURN
        DIVIDE(TOTALYTD(SUM('Data2'[$]), 'Data2'[Date].[Date]) - __PREV_YEAR, __PREV_YEAR)
    )


    This will compare the current YTD accumulation to previous year YTD and calculate the YOY growth. 

Tutu_in_YYC's avatar
Tutu_in_YYC
Icon for Super User rankSuper User
4 years ago

That tells me that you do not have data for previous year. Is that the case?

Anonymous's avatar
Anonymous
Not applicable
4 years ago

I do have data for previous year. Also, understand these are quarterly data. Let me get you a screen shot. 

  • Tutu_in_YYC's avatar
    Tutu_in_YYC
    Icon for Super User rankSuper User
    4 years ago

    My mistake, previous calculations will not work in your model.
    try this:

    YoY% for YTD = 

    IF(
        ISFILTERED('Data2'[Date]),
        ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
        VAR __PREV_YEAR = CALCULATE(TOTALYTD(SUM('Data2'[$]), 'Data2'[Date].[Date]), DATEADD('Data2'[Date].[Date], -1, YEAR))
        RETURN
        DIVIDE(TOTALYTD(SUM('Data2'[$]), 'Data2'[Date].[Date]) - __PREV_YEAR, __PREV_YEAR)
    )


    This will compare the current YTD accumulation to previous year YTD and calculate the YOY growth. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Thank you so much. I apprciate you. That worked right. 

     

  • Tutu_in_YYC's avatar
    Tutu_in_YYC
    Icon for Super User rankSuper User
    4 years ago

    glad to hear!

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi - What about in the case of wanting to calculate,

    example
    YTD = Q4 2021 - Q4 2020
    QTD = Q4 - Q3

  • Tutu_in_YYC's avatar
    Tutu_in_YYC
    Icon for Super User rankSuper User
    4 years ago

    YTD = Q4 2021 - Q4 2020 : As in the difference of the amount beween current year and previous year?

    QTD = Q4 - Q3 : Difference from current Quarter  to the previous Quarter ( i.e Qn - Qn-1)?

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Got this sorted. Thank you 

     

    But i have another question on how to write this R code in Dax. 

     

    Filter(!is.na(SentMonth), Surveytaken==2)

  • Tutu_in_YYC's avatar
    Tutu_in_YYC
    Icon for Super User rankSuper User
    4 years ago

    Ooh not proficient in R.

    But try this
    EOC_Sent = CALCULATE(countrows('Sheet1'), 'Sheet1'[SurveyTaken]=2)

     

    If it doesnt work, it means your table structure may not be correct.