Forum Discussion
Power Pivot running total of occurance DAX month index
Hello all,
I am trying to get an index of months in my calendar table using dax. I need a formula that gives me the Index result that looks like this:
| Year | Month | Day | Month Index |
| 2019 | Jan | 1 | 1 |
| 2019 | Jan | 2 | 1 |
| 2019 | Jan | 3 | 1 |
| 2019 | Feb | 1 | 2 |
| 2019 | Feb | 2 | 2 |
| 2019 | Feb | 3 | 2 |
| 2019 | Mar | 1 | 3 |
| 2019 | Mar | 2 | 3 |
| 2019 | Mar | 3 | 3 |
| 2020 | Jan | 1 | 4 |
| 2020 | Jan | 2 | 4 |
Any ideas?
4 Replies
BDMartin -
You should just be using Date values to build your Date Table like:
01/01/20 01/02/20 01/03/20 01/04/20 01/05/20 01/06/20 01/07/20 01/08/20 01/09/20 01/10/20 ...
Then using Calculated Columns as:
[Year] = YEAR([Date]) [MonthNum] = MONTH([Date]) [Month] = FORMAT([Date],"mmm") [DayNum] = DAY([Date])I don't understand the logic where 2020 Jan becomes Index = 4. Can you explain?
In this example, the index column is counting the 4th occurance of a beginning of a month. In reality the month count will go chronoligically over several years. I need this to get a Month (and then year) offset number to do time intelligence calculations in power pivot. (At least I think I do)
Thank you for your input, but I have the rest of the date table put together.
Is there a way to share just the Calendar table I have currently without putting in the rest of the workbook with its data model?
BDMartin -
To be honest I'm not sure this will help you when using Time Intelligence functions; I myself had to build a calendar with custom periods however I took that idea and included it in the attached.
It's using Power Query > Add to Data Model. Hopefully that is what you were going for.
