Adding an Index Column to a Power Pivot Model using EARLIER() DAX Function

~ Business Intelligist

This post will be a little bit atypical compared to what I normally write about. I usually don’t focus on topics like the one described below because there are plenty of other blogs that cover developer specific issues related to using DAX. However, I ran into an issue few days ago, could not find a solution on the web so I decided that maybe it would not be a bad idea to post this. Plus, this solution requires the use of the EARLIER() DAX function, and to be frank, it is one of the functions that are not always easy to understand, so I thought it might be a good idea to post another example on how one might use it.

The sample file can be found here…

The file contains a very simple date table that I created in Excel. I then used “Add to Data Model” button on the Power Pivot tab to create a linked table in Power Pivot. The Excel table only has these three columns

After that, I added a few more columns to the Power Pivot tab in order to illustrate the approach


Column Name

DAX

SimpleCount

=CALCULATE(COUNT([Date]))

CountAll

=CALCULATE(COUNT([Date]), ALL(‘Date’))

Index

=CALCULATE(COUNT([Date]), ALL(‘Date’), FILTER(‘Date’, [Date]<=EARLIER([Date])))

IndexByMonth

=CALCULATE(COUNT([Date]), ALL(‘Date’), FILTER(‘Date’, [Date]<=EARLIER([Date])), FILTER(‘Date’, [Month]=EARLIER([Month])))

 

Let’s take a quick look in what’s going on here… Every cell in the SimpleCount column is populated with 1 because the context for the COUNT function is reset with every row, so it essentially counts each row once.

CountAll column is populated with 541 for each cell because there are 541 rows in the original table, so for each row, ALL() function forces the COUNT to run against the entire table.

Index is populated with incremental numbers from 1 to 541 because for every row, the formula tries to count the entire table, as ALL would force, but the EARLIER filter limits the COUNT to all dates prior or equal to the current evaluated row.

IndexByMonth works similar to the Index calc but it resets the index with the start of each month.

Published by Business Intelligist

12 thoughts on “Adding an Index Column to a Power Pivot Model using EARLIER() DAX Function”

  1. Pingback: Bing vs. Google | business intelligist

  3. Should work the same way since we are using COUNT function but I would deal with duplicates in Power Query by using “Remove Duplicates” command before they make it into Power Pivot or Power BI Desktop.

    Reply

  4. the question was how to create an index in calculated column for a table ( such as a transactional table)with duplicates dates.

    Reply

  5. Thank you for sharing this – it’ll be very useful for time intelligence functions (such as with the use of the GFITW), using custom calendars.

    Reply

  6. I am in the same boat with Mohamed. I would need something works with duplicate transactions….have not been able to find anything like that

    Reply

  8. Try this…..

    ADDCOLUMNS (
    Table ,
    “Index” ,
    CALCULATE ( COUNTROWS ( Table ),
    FILTER (
    ALL ( Table ) ,
    Table[Col1] & Table[Col2] & Table[Col3]
    <= EARLIER ( Table[Col1 ) & EARLIER ( Table[Col2] ) & EARLIER ( Table[Col3] )
    )
    )
    )

    It's not elegant, but I think the basic pattern should work with as many Columns as you like for your Composite Key

    Reply

  9. ..come up with this quick&dirty solution (no exp with DAX) for the “indexing should consider duplicite dates as same index value”

    To do so, divide the index calculation by number of rows storing the same date:
    /CALCULATE(COUNTROWS(‘Date’),ALLEXCEPT(‘Date’,Date[Date]))
    and round it up for 0 decimal space.

    In whole:
    =ROUNDUP(
    CALCULATE(COUNT([Date]), ALL(‘Date’), FILTER(‘Date’, [Date]<=EARLIER([Date])), FILTER(‘Date’, [Month]=EARLIER([Month])))
    /
    CALCULATE(COUNTROWS('Date'),ALLEXCEPT('Date',Date[Date])),
    0)

    Reply

  10. I have a unique issue. We have a yyyymm text column; each time the data is updated a new yyyymm comes on. The problem is that the most recent 3 months are “preliminary” data and really only want the tables/charts to reflect the data without the preliminary months. Remember the data is updated monthly and adds months on. I’m stumped on how to get the data to drop the recent 3 months. Another caveat to this is we want to take those 3 preliminary months and have their own slicer so the users can see it if they want.

    Reply

    1. I’ve converted the yyyymm to a value and I have split off the month (mm) into its own value. This is going to be a look up table for my very large dataset.

      Reply

Leave a comment