March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
Hi all,
I need help with a custom hierarchy calendar. I have a 4 weeks per period calendar with 13 periods so 52 weeks each year. Uploaded test.pbix here for the simple sample data. https://1drv.ms/u/s!ArweH82sfYtEnHlOx78wkBU_I7Cu?e=HkxHzu
I already made a small visual with the year,period,week values, but the previous year,period,week does not work. I can not use the previousyear function because of the custom calendar.
Compare with | PY (measure) | Expected PreviousYear | |
2021 | 2020 | 37 | 38 |
P202011 | P201911 | 2 | 3 |
W202044 | W202044 | 0 | 1 |
In the table above you see that my PY (measure) gives me 37 where expected is 38 for the year 2021 compared with 2020. The PY (measure) gives me 2 where expected is 3 for the P202011 compared with P201911. The PY (measure) gives me 0 where expected is 1 for the W202044 compared with W201944.
I think I just needs some custom hierarchy or something, but I need to have the "previous" year values for my period and weeks.
Who could help me out?
Subtract 1 from the "current" year. In your filter set the period filter to a replaced text value including the new year
Let's say the Period is P202011 and the year is 2020
so the code would look like
PY =
var p = max(Period)
var y = max(year)
return calculate(sum(value),Period = substitute(p,format(y),format(y-1))
Thanks for your reply, does not work the way I would like it to work, besides that the function FORMAT needs at least 2 arguments.
I found a solution for the problem with the following measure:
If you have a custom calendar you need to create custom time intelligence functions. They will be especially hairy across year boundaries.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
114 | |
76 | |
57 | |
52 | |
44 |
User | Count |
---|---|
165 | |
116 | |
63 | |
57 | |
50 |