Prior Period with Weekly Calendar and 2 Calendar Tables

[Kaycee]

Hi all,

I'd appreciate any tips on writing prior period calculations using 2 calendar tables. This is to enable the user to select a reporting period using the 'Calendar Filters Only' table, but still show 'Year to Date' periods in a report. Hence if the user selected Aug-21, the report would still be able to show all the periods from January - August and not be restricted by a filter.

The challenge I am facing is that the prior year values are picking up correctly but they show in the prior year, not in the current year. Usually on a standard financial or Gregorian calendar I'd include SAMEPERIODLASTYEAR or DATEADD to adjust this. But that results in incorrect reporting using a weekly 5-4-4 calendar.

The current result is:

This is the structure:

Act Sales $ PYTD =

VAR _PriorYear = [Ref: Filter Year (yyyy)] - 1 -- THIS IS PULLING THE YEAR SELECTED IN THE FILTER OVER 'CALENDAR FILTERS ONLY'
VAR _CurrentWeek = [Ref: Filter Week (/Week / w)] -- THIS IS PULLING THE WEEK SELECTED IN THE FILTER OVER 'CALENDAR FILTERS ONLY'

RETURN
CALCULATE(
[Act Sales (Base Value)],
'Calendar'[Year (YYYY)] = _PriorYear,
'Calendar'[Week (/Week / w)] <= _CurrentWeek,
REMOVEFILTERS('Calendar Filters Only'),
USERELATIONSHIP('Calendar'[Date], 'Calendar Filters Only'[Filter Date])
)

I'm thinking possibly to write a calculated table as a variable then calculate the prior year numbers over that table, but I'm not exactly sure. I understand why it is doing this, but I am unclear on how to fix it. Any help would be appreciated.

[djurecicK2]

Hi @Kaycee ,

 I would not recommend using 2 date tables.

I agree with @lbendlin that your date table should have fiscal week numbers. With that in place, you can do something like this to get Fiscal WTD Last Year:

(Alter to fit your table and field names)

Fiscal WTD Last Year Value=

var _selectedFiscalWeek= SELECTEDVALUE(Datetable[FiscalWeekNumber],0)

var _selectedFiscalYear= SELECTEDVALUE(Datetable[FiscalYear],0)-1

return

CALCULATE([Your Measure],

Filter(All(Datetable),Datetable[FiscalWeekNumber]= _selectedFiscalWeek && Datetable[FiscalYear]= _selectedFiscalYear)

)

Please consider accepting as solution if this resolves the issue.

[lbendlin]

Does your custom calendar have week numbers?