Power Pivot running total of occurance DAX month index

[BDMartin]

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?

[#2]

@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?

[#3]

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?

@ChrisMendoza 

[#4]

@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.

[#5]

I wanted to keep it out of Power Querry, just using the automatic Calendar generated by Power Pivot. I think this will allow me to be able to use it with dynamic date ranges depending on the date ranges in my data set ( I'm pretty sure the bosses are eventually going to say "Hey, That's neat. Can we see the last five years now?" Those bosses get greedy once you start showing them things.)

Anyway I think I solved it. But I'm sure there's a more elegant solution. I created a couple of calculated columns.

[Day of Month}=FORMAT('Calendar'[Date],"dd")

[Day of Month Cheat]=IF('Calendar'[Day of Month #]="01",1,0)

[Month Sequence]=CALCULATE(SUM('Calendar'[Month Cheat]),FILTER('Calendar','Calendar'[Date]<=EARLIER('Calendar'[Date])))

[Month Offset]=-LOOKUPVALUE('Calendar'[Month Sequence #],'Calendar'[Date],TODAY())+'Calendar'[Month Sequence #]

@ChrisMendoza