Forum Discussion

unnijoy's avatar
unnijoy
Post Prodigy
1 year ago
Solved

Last 3 quarter

I have a data set in which contain Sales amount, Name of manager, Report pull date, Quarter I need to build a Matrix table that will show last 3 quarter sales for each manager based on the Quarter s...
Need Help
  • v-pnaroju-msft's avatar
    v-pnaroju-msft
    1 year ago

    Hi unnijoy,

    Thank you for your follow up.

    Please find attached a screenshot and a sample PBIX file that may help resolve the issue:


    We hope the information provided is useful. If you have any further queries, please feel free to contact the Microsoft Fabric community.

    Thank you.

Shahid12523's avatar
Shahid12523
Community Champion
1 year ago

Try this

Qtr_index =
RANKX (
ALL ( 'Calander'[YearQtr] ),
CALCULATE ( MIN ( 'Calander'[Date] ) ),
,
ASC,
DENSE
)

unnijoy's avatar
unnijoy
Post Prodigy
1 year ago

Shahid12523 , thank you. When i use this i am getting only 2 as the rank.

Am i missing somthing.

  • Shahid12523's avatar
    Shahid12523
    Community Champion
    1 year ago

    If YearQtr is numeric (e.g., 202301, 202302):

     

    Qtr_index =
    RANKX (
    ALL ( Calander[YearQtr] ),
    MAX ( Calander[YearQtr] ),
    ,
    ASC,
    Dense
    )

     

     

    If YearQtr is text → rank on start date:

     

    Qtr_index =
    RANKX (
    ALL ( Calander[Qtr Start date] ),
    MAX ( Calander[Qtr Start date] ),
    ,
    ASC,
    Dense
    )

    • unnijoy's avatar
      unnijoy
      Post Prodigy
      1 year ago

      Shahid12523 
      Now every thing is coming as 1.

       

       

  • Shahid12523's avatar
    Shahid12523
    Community Champion
    1 year ago

    What’s happening:

    CALCULATE(MIN(Calander[Date])) → always returns the quarter start date.

    Since multiple quarters may share the same MIN logic depending on your context, you’re ending up with the same rank (2 in your case).