Forum Discussion

stfox's avatar
stfox
Helper I
10 years ago
Solved

DAX: Ranking or Indexing Dates

Hi Folks I have a Pivot that creates a Cohort Anlaysis as shown on the left below.  This shows the number of customer remaining in a cohort in the subsequent months (invoice months) following signup...
Need Help
  • v-sihou-msft's avatar
    v-sihou-msft
    10 years ago

    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.
Sean's avatar
Sean
Community Champion
10 years ago

stfox If you are analyzing only 12 months at a time (1 Calendar Year) you could simply add a Calculated Column like so

 

DAX Index = MONTH ( 'Table'[Invoice_Month] ) - 1

 

 

 

Of course then you would have to adjust OwenAuger's solution here

http://community.powerbi.com/t5/Desktop/Cohort-Analysis-DAX/m-p/40102#M15035

To something like this...

 

Measure DAX Index = 
DIVIDE (
    [Customer ID Count],
    CALCULATE (
        [Customer ID Count],
        GENERATE (
            VALUES ( 'Table'[Signup Month] ),
            FILTER (
                ALL ( 'Table'[DAX Index] ),
                'Table'[DAX Index] = MONTH('Table'[Signup Month])-1
            )
        )
    )
)

 

 

If you are however doing this on an ongoing basis - the Index would have to be constructed differently!

  • stfox's avatar
    stfox
    Helper I
    10 years ago

    Thanks Sean, yes the hook is that the time period is greater than 12 months, and grows through time. Any suggestions ?

    Cheers

    Steve

    • v-sihou-msft's avatar
      v-sihou-msft
      Microsoft Employee
      10 years ago

      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.
      • stfox's avatar
        stfox
        Helper I
        10 years ago

        Thanks Simon - Just what I was after. Totally logical - when broken down into those steps

        Cheers Steve

         

        BTW - The working example is here - for anyone who requires it in the future