topic Re: DAX DateAdd in Power BI in DAX Commands and Tips

DAX DateAdd in Power BI

Anonymous — Thu, 30 Jun 2022 18:36:53 GMT

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.

Re: DAX DateAdd in Power BI

amitchandak — Fri, 01 Jul 2022 03:31:15 GMT

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.

Re: DAX DateAdd in Power BI

Anonymous — Tue, 05 Jul 2022 09:32:04 GMT

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