Calendar Week Comparison to last year with a calculation group

[Anonymous]

Hi everyone, 

I am trying to write a calculation group which calculates the selected measure in the same calendar week from the last year. 

This is how it looks in the Report: (I recreated this in Excel with imaginary numbers to secure customer data) 

I have the yearweek slicer and depending on what month i choose there, my table will calculate. 

It is a matrix table with the comparison calculation in the columns and the different KPIs (revenue, Klicks, CTR) as values. The KPIs are simple Sum Calculations.

The problem is that I tried to validate my data but I got a value  for the prior year of +54% when in reality it should be -35%. So I think I maybe did something wrong in the calculations.

Now I do realize in my calculations are two different calendars, but that is intentional, we need those two in the report and as I understand we wrote the calculation to work on both calendars.

The format of JahrKWSonntag (which is YearWeek) is 202109, etc.

My current calculation grouup consints of two calculations:

Actual =

VAR _CURRENT_YEARWEEKSUNDAY =
SELECTEDVALUE ( 'Kalender 2'[JahrKWSonntag] )

RETURN

CALCULATE (

SELECTEDMEASURE (),
KEEPFILTERS (

FILTER (

ALL ( 'Kalender' ),
[JahrKWSonntag] = _CURRENT_YEARWEEKSUNDAY
)

)

)

Prior Year = 

VAR _CURRENT_YEARWEEKSUNDAY =
SELECTEDVALUE ( 'Kalender 2'[JahrKWSonntag] )

VAR _VJ =

CALCULATE (

SELECTEDMEASURE (),
KEEPFILTERS (

CALCULATETABLE (

FILTER (

ALL ( 'Kalender' ),
[JahrKWSonntag] = _CURRENT_YEARWEEKSUNDAY - 100
)

)

)

)

RETURN

DIVIDE (

CALCULATE (

SELECTEDMEASURE (),
KEEPFILTERS (

FILTER (

ALL ( 'Kalender' ),
[JahrKWSonntag] = _CURRENT_YEARWEEKSUNDAY
)

)

) - _VJ,
_VJ

)

@tabulareditor @calculationgroup

[ValtteriN]

Hi,

Here is a good discussion about a similar topic:
 https://forum.enterprisedna.co/t/same-period-last-year-for-fiscal-week-nbr-weeks-in-sales-table-are-...

In essence something like this should work:

Previous Year Same Week Calc group=
VAR CurrentMonth = SELECTEDVALUE( Dates[Week Number] )
VAR CurrentYear = SELECTEDVALUE( Dates[Year] ) RETURN
CALCULATE( SELECTEDMEASURE (), FILTER( ALL( Dates ), Dates[Week Number] = CurrentMonth && Dates[Year] = CurrentYear - 1))

I hope this helps to solve your issue and if it does consider accepting this as a solution and giving the post a thumbs up!