Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
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=
Does your custom calendar have week numbers?
User | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |