Same day last year sales.

[sadath]

Hi

I have DAX to calculate same day sales last year which is working fine

SameDayLY:=calculate(sum(SALES[SALESLY]),dateadd(SALES[DATE],+1,day))

after 29th Feb the formula should be

SameDayLY2:=calculate(sum(SALES[SALESLY]),dateadd(SALES[DATE],+2,day))

when i combine the two formula it is not working

ie SameDayLy3:=if(SALES[DATE]>=date(2016,3,1),[SameDayLY2],[SameDayLY])

or any alternate formula

Thanks

 

[Matt Allington]

Try this. SameDayLy3:=if(max(SALES[DATE])>=date(2016,3,1),[SameDayLY2],[SameDayLY])

 

[Matt Allington]

Leap years are complex. It depends on the business rules. It sounds like your business rules say "Same day, same month but prior year". This implies that 29th Feb is compared against nothing. And next year there will be 1 day last year not compared againt the new year.

So so maybe you need something like this

=calculate(sum(sales[sales ty]),dateadd(calendar[date],-12,month))

also it looks like you don't have a calendar table. This is mandatory for time intelligence. Read about it here Power Pivot Calendar Tables -

 

[sadath]

Hi
Thank you very much,

if(max(SALES[DATE])..... works fine,