Quarter and Week Aging

[etuckeriv]

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?

[etuckeriv · Accepted 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!

[mahoneypat]

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

[Greg_Deckler]

@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