topic Re: YTD % DAX calculation. in DAX Commands and Tips

YTD % DAX calculation.

Anonymous — Mon, 28 Mar 2022 19:52:26 GMT

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 %, which give me the result I want but when I tried using same format to calculate YTD % i was getting an error.

Re: YTD % DAX calculation.

Tutu_in_YYC — Mon, 28 Mar 2022 20:09:44 GMT

Hi Rilwan,
try this:

YTD% =
VAR _Current = TOTALYTD(SUM(Data2[$]), Data2[Date])
VAR _Previous = CALCULATE( TOTALYTD(SUM(Data2[$]), Data2[Date]), SAMEPERIODLASTYEAR(Data2[Date]))
RETURN
DIVIDE( _Current - _Previous, _Previous, 0)

Re: YTD % DAX calculation.

Anonymous — Mon, 28 Mar 2022 20:17:03 GMT

Thank you so much for the quick reply. I did try but only getting 0 values.

Re: YTD % DAX calculation.

Tutu_in_YYC — Mon, 28 Mar 2022 21:08:19 GMT

Do you have a year slicer in the report? If yes, can you choose a year?

Re: YTD % DAX calculation.

Anonymous — Mon, 28 Mar 2022 21:11:45 GMT

Yes, i did choose a year and still getting 0 values.

Re: YTD % DAX calculation.

Tutu_in_YYC — Mon, 28 Mar 2022 21:14:16 GMT

Can you create this for validation, and put it in the matrix? Upload a snapshot.

Validation =
VAR _Current = TOTALYTD(SUM(Data2[$]), Data2[Date])
VAR _Previous = CALCULATE( TOTALYTD(SUM(Data2[$]), Data2[Date]), SAMEPERIODLASTYEAR(Data2[Date]))
RETURN
_Current & "---" & _Previous

Re: YTD % DAX calculation.

Anonymous — Mon, 28 Mar 2022 21:19:19 GMT

Re: YTD % DAX calculation.

Tutu_in_YYC — Mon, 28 Mar 2022 21:24:00 GMT

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

Re: YTD % DAX calculation.

Anonymous — Mon, 28 Mar 2022 21:28:54 GMT

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

Re: YTD % DAX calculation.

Anonymous — Mon, 28 Mar 2022 21:36:06 GMT

Re: YTD % DAX calculation.

Tutu_in_YYC — Mon, 28 Mar 2022 21:44:25 GMT

What year are you looking at in the below snapshot?

Re: YTD % DAX calculation.

Tutu_in_YYC — Mon, 28 Mar 2022 22:16:11 GMT

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.

Re: YTD % DAX calculation.

Anonymous — Tue, 29 Mar 2022 16:55:59 GMT

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

Re: YTD % DAX calculation.

Tutu_in_YYC — Tue, 29 Mar 2022 22:44:57 GMT

glad to hear!

Re: YTD % DAX calculation.

Anonymous — Thu, 31 Mar 2022 17:41:33 GMT

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

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

Re: YTD % DAX calculation.

Tutu_in_YYC — Thu, 31 Mar 2022 19:40:03 GMT

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)?

Re: YTD % DAX calculation.

Anonymous — Wed, 06 Apr 2022 16:23:52 GMT

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)

Re: YTD % DAX calculation.

Tutu_in_YYC — Wed, 06 Apr 2022 18:39:16 GMT

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.