DAX: Ranking or Indexing Dates

stfox

In this scenario, we can rank Invoice Month (from earliest month) within each Signup Month group, and use the rank number as the column group in Matrix. Please refer to following steps:

1. Create a measure for the number of customers.
   

   TotalCustomer = CALCULATE(COUNTROWS('Raw Cohort data'))

2. Since we will rank the month values, we need to convert the Invoice date into number.
   

   Inv_Year&Month = 
   VALUE (
       YEAR ( 'Raw Cohort data'[Invoice_Month] ) & "0"
           & MONTH ( 'Raw Cohort data'[Invoice_Month] )
   )
   

3. Create a column for the rank by Inv_Year&Month within Signup month. You can see the rank in below table chart.
   

   RankInvoiceYear&Month = 
   RANKX (
       FILTER (
           'Raw Cohort data',
           EARLIER ( 'Raw Cohort data'[Signup Month] ) = 'Raw Cohort data'[Signup Month]
       ),
       'Raw Cohort data'[Inv_Year&Month],
       ,
       ASC,
       DENSE
   )
   

4. Drag below data fields into the Matrix chart. Since you already know the DAX for the % view, you can replace “RankInvoiceYear&Month” with the % view DAX and then you can get another % view Matrix.