Highlighted
New Contributor

# 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
Highlighted

# Re: Power Pivot running total of occurance DAX month index

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?

Highlighted

# Re: Power Pivot running total of occurance DAX month index

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?

Highlighted

# Re: Power Pivot running total of occurance DAX month index

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.

Highlighted

# Re: Power Pivot running total of occurance DAX month index

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 #]