Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I have a fiscal calendar lookup table that I'm looking to create a couple of extra calculations in, but I can't seem to figure out how to get them to do what I need.
What I need are what my company calls "Quarter Aging" and "Week Aging", and basically the formula should assign the value "0" for the current quarter/week, and then count (positive or negative) the quarters/weeks away from the current one.
Example: Current quarter is assigned the value of "0", last quarter would be "-1", next quarter would be "1" all the way up and down the column (with the current quarter/week being somewhere in the middle of the data).
I have caluclations for week index and quarter index, as well as calculations that identify the current quarter and current week. Does anyone know how I can do this?
Solved! Go to Solution.
This actually turned out to be pretty simple. All I ended up doing was subtracting the Week/Quarter Index Number from the current Quarter/Week Index Number with this formula:
=FiscalCalendarTable[QuarterIndex]-LOOKUPVALUE(FiscalCalendarTable[QuarterIndex],FiscalCalendarTable[Today?],TRUE)
Thanks for the guidance, everyone! I learned about a couple new functions while reverse engineering Greg's solutions below!
This actually turned out to be pretty simple. All I ended up doing was subtracting the Week/Quarter Index Number from the current Quarter/Week Index Number with this formula:
=FiscalCalendarTable[QuarterIndex]-LOOKUPVALUE(FiscalCalendarTable[QuarterIndex],FiscalCalendarTable[Today?],TRUE)
Thanks for the guidance, everyone! I learned about a couple new functions while reverse engineering Greg's solutions below!
You can use this column expression on your Date table to get a quarter index that is 0 in the current quarter.
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@etuckeriv - So, seems like an application of Sequential:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Sequential/m-p/380231#M116
Can you provide sample data and expected output?
I also just posted this custom DAX 445 custom calendar which some of the logic in there may help as well:
https://community.powerbi.com/t5/Quick-Measures-Gallery/DAX-Custom-445-Calendar/td-p/1388582
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
25 | |
10 | |
7 | |
6 | |
6 |
User | Count |
---|---|
30 | |
11 | |
11 | |
10 | |
6 |