Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Seeking help for Previous Year-Week Calculation

I need to calculate the Previous Year-Week value(PY) based on the selected Year-Weel from Slicer. Then I need to calculate that PY value for last 4 Weeks, 12 Weeks and 52 Weeks.    This is how I ca...
Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi,

Thanks for the prompt reply. In my case, Last Weekly Year sales is different for Leap year and non leap years. In that case "SAMEPERIODLASTYEAR" is not giving the correct result. That's why I have to take different approach. Also I have a calculated Group which is Week, Last 4 Weeks, Last 12 Weeks and Last 52 Weeks that has to be apply on these PY as well. Which is i am unable to find out. If you can see my DAX i tried to create a measure for Last 4 Weeks but that calculation gave me Weekly result. 

Below is my model:-

 

 

 

Whitewater100's avatar
Whitewater100
Icon for Solution Sage rankSolution Sage
4 years ago

Hi:

I like ptting week no in my date table and usingthat to filter the expression.

 

DATEADD works well    LY Sales = CALCULATE[Sales Amt], DATEADD(Dates[Date], -1,YEAR))

 

If you want to flag for leap year and create a custom measure you can have a calculated column in DATE table.

ISLEAPYEAR =YearStartDate = DATE(‘Year'[Year],01,01)

YearEndDate = DATE(‘Year'[Year],12,31)

TotalDays = (‘Year'[YearEndDate]-‘Year'[YearStartDate])*1+1

IsLeapyear = IF(‘Year'[TotalDays]=366,”Yes”,”No”)

and to exclude the extra date a measure for LY:

LY Sales =

        if( hasonevalue(Dates[Date]),

                 var vcurrentDate = max(Date[Date])

                 var vdocalculation =

                         month(vcurrentDate) <> 2

                         && day(vcurrentDate) <> 29

                 var vresult =

                         if(vdocalculation,

                                  CALCULATE(

                                          [Sales Amt],

                                          DATEADD(Date[Date],-1,YEAR)

                                  )

                         )

                 return

                         vresult

        )

 

That's about all I've seen, so far, on this topic. Hope it helps!