Forum Discussion

etuckeriv's avatar
etuckeriv
Frequent Visitor
6 years ago
Solved

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 compa...
  • etuckeriv's avatar
    etuckeriv
    6 years ago

    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's avatar
mahoneypat
Icon for Microsoft Employee rankMicrosoft Employee
6 years ago

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