Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi all,
I have a calendar table with 4 weeks per period and 13 periods per year. I also have a few columns like SeqPeriod and SeqWeek, these start with 1 up to the end of the calendar table.
So the problem is, in this 4 weeks calendar we can have Day 1 of Week 1 of Period 1 that COULD be 30 december of the year before. So we can not use the Sameperiodlastyear function.
So for Example I have a matrix with Year 2021, Period P202101 to P202113 and week W202101 to W202152 and a value for orders. I would like to see the orders for sameperiod last year. So in 2021 I would like to see a value for 2021 for example 100 and a value for sameperiodlast year so 2020 for example 80. When I Expand to periods I would like to see a value for P202101 and for sameperiodlastyear so for P202001, same for weeks.
Who could help me out?
Good to know,
The calendar has
week 1 to 52 for each year
period 1 to 13 for each year.
WHen I select YearPeriod 202203 I need the value of 202103 but this could be different dates.
Hi,
Thanks for your reply, i have tried this earlier and then it works only on my year level. expanding to period and weeks does not work. But thanks for the help.
hmm i think you have to write your own time intelligence functions and use numerical ID columns for periods that you can use as you write your measure.
an example:
Rolling 6 Months Sales =
CALCULATE([Total Sales],
FILTER(ALL('Calendar'),
'Calendar'[Month ID] >= MAX('Calendar'[Month ID])-5
&& 'Calendar'[Month ID] <= MAX('Calendar'[Month ID])
)
)
although im not sure if you can use both periodic and annual comparison on a single measure on matrix but maybe others have some thoughts!
User | Count |
---|---|
92 | |
85 | |
77 | |
66 | |
62 |
User | Count |
---|---|
110 | |
95 | |
95 | |
64 | |
59 |