Forum Discussion

etuckeriv's avatar
etuckeriv
Frequent Visitor
5 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 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's avatar
    etuckeriv
    5 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!

3 Replies

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    5 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
     
  • etuckeriv's avatar
    etuckeriv
    Frequent Visitor
    5 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!