Power Pivot running total of occurance DAX month index

%3CLINGO-SUB%20id%3D%22lingo-sub-1211939%22%20slang%3D%22en-US%22%3EPower%20Pivot%20running%20total%20of%20occurance%20DAX%20month%20index%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1211939%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20all%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20get%20an%20index%20of%20months%20in%20my%20calendar%20table%20using%20dax.%20I%20need%20a%20formula%20that%20gives%20me%20the%20Index%20result%20that%20looks%20like%20this%3A%3C%2FP%3E%3CTABLE%20width%3D%22289%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2264%22%3EYear%3C%2FTD%3E%3CTD%20width%3D%2264%22%3EMonth%3C%2FTD%3E%3CTD%20width%3D%2264%22%3EDay%3C%2FTD%3E%3CTD%20width%3D%2297%22%3EMonth%20Index%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2019%3C%2FTD%3E%3CTD%3EJan%26nbsp%3B%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2019%3C%2FTD%3E%3CTD%3EJan%26nbsp%3B%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2019%3C%2FTD%3E%3CTD%3EJan%26nbsp%3B%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2019%3C%2FTD%3E%3CTD%3EFeb%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2019%3C%2FTD%3E%3CTD%3EFeb%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2019%3C%2FTD%3E%3CTD%3EFeb%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2019%3C%2FTD%3E%3CTD%3EMar%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2019%3C%2FTD%3E%3CTD%3EMar%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2019%3C%2FTD%3E%3CTD%3EMar%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2020%3C%2FTD%3E%3CTD%3EJan%26nbsp%3B%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E4%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2020%3C%2FTD%3E%3CTD%3EJan%26nbsp%3B%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E4%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20ideas%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1211939%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1211975%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Pivot%20running%20total%20of%20occurance%20DAX%20month%20index%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1211975%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F575427%22%20target%3D%22_blank%22%3E%40BDMartin%3C%2FA%3E%26nbsp%3B-%3C%2FP%3E%3CP%3EYou%20should%20just%20be%20using%20Date%20values%20to%20build%20your%20Date%20Table%20like%3A%3C%2FP%3E%3CTABLE%20border%3D%220%22%20width%3D%2280%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2280%22%20height%3D%2222%22%3E01%2F01%2F20%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2222%22%3E01%2F02%2F20%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2222%22%3E01%2F03%2F20%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2222%22%3E01%2F04%2F20%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2222%22%3E01%2F05%2F20%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2222%22%3E01%2F06%2F20%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2222%22%3E01%2F07%2F20%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2222%22%3E01%2F08%2F20%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2222%22%3E01%2F09%2F20%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2222%22%3E01%2F10%2F20%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%20using%20Calculated%20Columns%20as%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%5BYear%5D%20%3D%20YEAR(%5BDate%5D)%0A%0A%5BMonthNum%5D%20%3D%20MONTH(%5BDate%5D)%0A%5BMonth%5D%20%3D%20FORMAT(%5BDate%5D%2C%22mmm%22)%0A%0A%5BDayNum%5D%20%3D%20DAY(%5BDate%5D)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20don't%20understand%20the%20logic%20where%202020%20Jan%20becomes%20Index%20%3D%204.%20Can%20you%20explain%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1212017%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Pivot%20running%20total%20of%20occurance%20DAX%20month%20index%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1212017%22%20slang%3D%22en-US%22%3E%3CP%3EIn%20this%20example%2C%20the%20index%20column%20is%20counting%20the%204th%20occurance%20of%20a%20beginning%20of%20a%20month.%20In%20reality%20the%20month%20count%20will%20go%20chronoligically%20over%20several%20years.%20I%20need%20this%20to%20get%20a%20Month%20(and%20then%20year)%20offset%20number%20to%20do%20time%20intelligence%20calculations%20in%20power%20pivot.%20(At%20least%20I%20think%20I%20do)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20input%2C%20but%20I%20have%20the%20rest%20of%20the%20date%20table%20put%20together.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20a%20way%20to%20share%20just%20the%20Calendar%20table%20I%20have%20currently%20without%20putting%20in%20the%20rest%20of%20the%20workbook%20with%20its%20data%20model%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F303863%22%20target%3D%22_blank%22%3E%40ChrisMendoza%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1212074%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Pivot%20running%20total%20of%20occurance%20DAX%20month%20index%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1212074%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F575427%22%20target%3D%22_blank%22%3E%40BDMartin%3C%2FA%3E%26nbsp%3B-%3C%2FP%3E%3CP%3ETo%20be%20honest%20I'm%20not%20sure%20this%20will%20help%20you%20when%20using%20Time%20Intelligence%20functions%3B%20I%20myself%20had%20to%20build%20a%20calendar%20with%20custom%20periods%20however%20I%20took%20that%20idea%20and%20included%20it%20in%20the%20attached.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt's%20using%20Power%20Query%20%26gt%3B%20Add%20to%20Data%20Model.%20Hopefully%20that%20is%20what%20you%20were%20going%20for.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1212079%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Pivot%20running%20total%20of%20occurance%20DAX%20month%20index%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1212079%22%20slang%3D%22en-US%22%3E%3CP%3EI%20wanted%20to%20keep%20it%20out%20of%20Power%20Querry%2C%20just%20using%20the%20automatic%20Calendar%20generated%20by%20Power%20Pivot.%20I%20think%20this%20will%20allow%20me%20to%20be%20able%20to%20use%20it%20with%20dynamic%20date%20ranges%20depending%20on%20the%20date%20ranges%20in%20my%20data%20set%20(%20I'm%20pretty%20sure%20the%20bosses%20are%20eventually%20going%20to%20say%20%22Hey%2C%20That's%20neat.%20Can%20we%20see%20the%20last%20five%20years%20now%3F%22%20Those%20bosses%20get%20greedy%20once%20you%20start%20showing%20them%20things.)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnyway%20I%20think%20I%20solved%20it.%20But%20I'm%20sure%20there's%20a%20more%20elegant%20solution.%20I%20created%20a%20couple%20of%20calculated%20columns.%3C%2FP%3E%3CP%3E%5BDay%20of%20Month%7D%3DFORMAT('Calendar'%5BDate%5D%2C%22dd%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%5BDay%20of%20Month%20Cheat%5D%3DIF('Calendar'%5BDay%20of%20Month%20%23%5D%3D%2201%22%2C1%2C0)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%5BMonth%20Sequence%5D%3DCALCULATE(SUM('Calendar'%5BMonth%20Cheat%5D)%2CFILTER('Calendar'%2C'Calendar'%5BDate%5D%26lt%3B%3DEARLIER('Calendar'%5BDate%5D)))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%5BMonth%20Offset%5D%3D-LOOKUPVALUE('Calendar'%5BMonth%20Sequence%20%23%5D%2C'Calendar'%5BDate%5D%2CTODAY())%2B'Calendar'%5BMonth%20Sequence%20%23%5D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F303863%22%20target%3D%22_blank%22%3E%40ChrisMendoza%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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:

YearMonthDayMonth Index
2019Jan 11
2019Jan 21
2019Jan 31
2019Feb12
2019Feb22
2019Feb32
2019Mar13
2019Mar23
2019Mar33
2020Jan 14
2020Jan 24

 

Any ideas?

4 Replies
Highlighted

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

 

Highlighted

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 

Highlighted

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

Highlighted

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