Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Kaycee
Advocate I
Advocate I

Prior Period with Weekly Calendar and 2 Calendar Tables

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:
Matrix.PNG

This is the structure:
Structure.PNG

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.

2 REPLIES 2
djurecicK2
Super User
Super User

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
Super User
Super User

Does your custom calendar have week numbers?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.