Seeking help for Previous Year-Week Calculation

[Anonymous]

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 calculated the PY for Year_Week Ex:- 202053 and then calculating 4 Weeks but result is same even If I used -28 Days for 4 Weeks or -84 Days for 12 Weeks. For Each Scenario I am getting same result. Seems like DatesInPeriod is not working fine. Is there any way to fix this issue.

testPY =

var _PY = IF (

HASONEVALUE ( 'src_period'[YEAR_WEEK] ),

SWITCH (

TRUE (),

MOD(VALUES ( src_period[YEAR] ),4) = 0 && RIGHT ( VALUE ( VALUES ( src_period[YEAR_WEEK] ) ), 2 ) = "53" ,

CALCULATE (

[Value],

src_period[YEAR_WEEK]

= MAXX ( src_period, src_period[YEAR_WEEK] - 52 )

)

)

)

RETURN CALCULATE (

_PY,

DATESINPERIOD (

src_period[PERIOD_END_DATE],

LASTDATE ( src_period[PERIOD_END_DATE] ),

-28,

DAY

)

)

[Whitewater100]

Hi:

Do you have model with sample data to share? If you have your set up along the lines of this image below, you can use time intelligence functions to do the calculations you want much easier. You need a separate date table, marked as a date table which connects to your fact table. In your calendar you can add year and week number, and use this in your slicer.

Then your measures are fairly easy.

 Sales Amount= SUM(SalesTable[Sales amt])

LY Sales = CALCULATE(Total Sales], SAMEPERIODLASTYEAR(Dates[Date]))

[Anonymous]

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]

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!