Forum Discussion

GuillaumeB's avatar
GuillaumeB
Icon for Helper I rankHelper I
6 years ago

Custom QTD Calculations

I'm having difficulties with calculating Quarter to date measures due to the fact that the quarters are designated by very specific dates, therefore I can't just offshift by months or weeks to accomo...
Need Help
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

GuillaumeB , missed columns

Add Qtr = QUOTIENT(DATEDIFF(Dates[Start of Year], Dates[Date],MONTH),3)*3
Qtr Rank = RANKX(ALL(Dates),Dates[Strat of Qtr],,ASC,Dense)
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

GuillaumeB , it can dynamic, if the start of the year is the same date every year like 12/30. If not we need to build qtr start logic

  • GuillaumeB's avatar
    GuillaumeB
    Icon for Helper I rankHelper I
    6 years ago

    I'm not sure what I SHOULD be getting as a result but this isn't right. Here is a sample from Q1 to show you 

    DateWeekEndingQuarterStart of YearStrat of QtrAdd QtrQtr Rank
    2/24/20202/28/2020Q112/29/2019 0:0012/1/2019 0:00021
    2/25/20202/28/2020Q112/29/2019 0:0012/1/2019 0:00021
    2/26/20202/28/2020Q112/29/2019 0:0012/1/2019 0:00021
    2/27/20202/28/2020Q112/29/2019 0:0012/1/2019 0:00021
    2/28/20202/28/2020Q112/29/2019 0:0012/1/2019 0:00021
    2/29/20202/28/2020Q112/29/2019 0:0012/1/2019 0:00021
    3/1/20203/6/2020Q112/29/2019 0:003/1/2020 0:00321
    3/2/20203/6/2020Q112/29/2019 0:003/1/2020 0:00321
    3/3/20203/6/2020Q112/29/2019 0:003/1/2020 0:00321

     

    Here is what I'm getting. The Quarter column is what I SHOULD be using as a quarter indicator but instead the Add Qtr is 0 until Feb 29th and then starts at 3 but our Q2 doesn't start until Apr-24th. Again, this isn't normal, all equal quarters, hence why I need to use a set of custom dates.