cancel
Showing results for 
Search instead for 
Did you mean: 
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
Memorable Member
Memorable Member

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.

If this post helps, then please consider accepting it as the solution to help other members find it more quickly.
lbendlin
Super User
Super User

Does your custom calendar have week numbers?

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.