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.