Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
etuckeriv
Frequent Visitor

Quarter and Week Aging

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?

1 ACCEPTED SOLUTION
etuckeriv
Frequent Visitor

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!

View solution in original post

3 REPLIES 3
etuckeriv
Frequent Visitor

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!

mahoneypat
Microsoft Employee
Microsoft Employee

You can use this column expression on your Date table to get a quarter index that is 0 in the current quarter.

 

Quarter Index =
var todayquarterindex = YEAR(TODAY())*4 + QUARTER(TODAY())
var thisindex = YEAR('Date'[Date])*4 + QUARTER('Date'[Date])
return thisindex - todayquarterindex
 
A similar approach can be used for Week Index
 
Week Index =
var todaystartofweek = TODAY() - WEEKDAY(TODAY())+1
var thisstartofweek = 'Date'[Date] -WEEKDAY('Date'[Date]) + 1
return DATEDIFF(todaystartofweek, thisstartofweek, DAY)/7
 
Regards,
Pat
 




Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Greg_Deckler
Community Champion
Community Champion

@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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.