DAX DateAdd in Power BI

[Anonymous]

Hi all,

I need help with the YOY function.

I have created a Calendar table for a continuous date. I have tried DateADD and SameperiodLastYear both to get YOY. 

YOY_Actual = CALCULATE(SUM(Com[Actual]),DATEADD('Calendar'[Date],-1,YEAR))

SamePeriodLastYear = CALCULATE(SUM(Com[Actual]),SAMEPERIODLASTYEAR('Calendar'[Date]))

The issue is, that I get the same value as the current year. I checked DATEADD('Calendar'[Date],-1,YEAR) function date. It shows the previous year's date. But when I run this function, it shows the current value instead of the previous year's value. Please help me.

[v-yiruan-msft · Accepted solution]

Hi  @Anonymous,

You can update the formula of measure [YOY_Actual] and [SamePeriodLastYea] as below, please find the details in the attachment.

YOY_Actual =
CALCULATE (
    SUM ( Com[Actual] ),
    DATEADD ( 'Calendar'[Date], -1, YEAR ),
    ALLSELECTED ( 'Com' )
)

SamePeriodLastYear =

CALCULATE (

SUM ( Com[Actual] ),

SAMEPERIODLASTYEAR ( 'Calendar'[Date] ),

ALLSELECTED ( 'Com' )

)

Best Regards

[amitchandak]

@Anonymous , Both these should give you last year value

YOY_Actual = CALCULATE(SUM(Com[Actual]),DATEADD('Calendar'[Date],-1,YEAR))
SamePeriodLastYear = CALCULATE(SUM(Com[Actual]),SAMEPERIODLASTYEAR('Calendar'[Date]))

The calendar table should be marked date tbale

Slicer and visual should use the period from the calendar whenever needed

Single directional join

refer

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.