cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

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. So for example in Jan 2016, the cohort started with 40 customers, and by March 2016 the number of customers was reduced to 22.

 

 I want  the date row (invoice Month) to be "indexed". So the first month the customer is invoiced to be 0, the second month to be 1, the third to be 3 (as shown on the right). In reality it doesn't matter if the index starts at 1 rather than 0.  The link to the PowerPivot & associated DAX (that creates the % view)  is here.

 

Any help or advice would would be much appreciated. 

 

Steve

 

 

Cohort analysis.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: 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.
    123.png

View solution in original post

8 REPLIES 8
Highlighted
Community Champion
Community Champion

Re: DAX: Ranking or Indexing Dates

@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

 

 

Cohort.png

 

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!

Highlighted
Helper I
Helper I

Re: DAX: Ranking or Indexing Dates

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

Cheers

Steve

Highlighted
Microsoft
Microsoft

Re: 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.
    123.png

View solution in original post

Highlighted
Helper I
Helper I

Re: DAX: Ranking or Indexing Dates

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

 

 

 

Highlighted
Impactful Individual
Impactful Individual

Re: DAX: Ranking or Indexing Dates

@stfox @v-sihou-msft @Sean

 

What a great thread.  Thanks Steve for posting your finished example.  Cheers!  Tom

Highlighted
Advocate I
Advocate I

Re: DAX: Ranking or Indexing Dates

Thank you so much! This has been really helpful! Smiley Happy

Highlighted
Helper III
Helper III

Re: DAX: Ranking or Indexing Dates

Hello

 

I am posting here cos I have a similar issue. I simply want to rank my reporting date.

 

Reporting date     Rank

1/21/2018              0

1/19/2018               -1

1/21/2018              0

1/17/2018              -2

Highlighted
Helper V
Helper V

Re: DAX: Ranking or Indexing Dates

I did the exact process but I didn't get the results like yours, can I send you the file to check why not work?

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors