CODEWITHSUNDEEP
exceldaxpowerquery
Bjorn Morrhaye
Bjorn Morrhaye

Reputation: 731

DAX calculate index based on month

in power query i have a table like this: 

date       | month    |
01/01/2017 | jan 2017 |
02/01/2017 | jan 2017 |
03/01/2017 | jan 2017 |
...        | ...      |
01/02/2017 | feb 2017 |
...
01/02/2017 | mar 2017 |
...
01/01/2018 | jan 2018 |

now i want to add a calculated row that gives me an index based on month. So for every new month the index needs to increase with 1. 

index | date       | month    |
01    | 01/01/2017 | jan 2017 |
01    | 02/01/2017 | jan 2017 |
01    | 03/01/2017 | jan 2017 |
..    | ...        | ...      |
02    | 01/02/2017 | feb 2017 |
..    | ...        | ...
03    | 01/03/2017 | mar 2017 |
..    | ...        | ... 
13    | 01/01/2018 | jan 2018 |

Anybody knows how ?

Upvotes: 1

Views: 5982

Answers (3)

Marc Pincince
Marc Pincince

Reputation: 5202

Here's a Power Query based approach:

Starting with this table as "Table1":

enter image description here

Click on the "Month" column, then the "Transform" tab, then the "Group By" button; and complete the dialog box like this:

enter image description here

...and click "OK."

You'll now have a table that looks like this:

enter image description here

Add an index column by clicking the "Add Column" tab and then the "Index Column" button.

You'll now have a table that looks like this:

enter image description here

Click the enter image description here button at the top right of the "AllData" column to expand the tables in the "AllData" column.

You'll now have a table that looks like this:

enter image description here

Drag the columns to arrange them into the order you want:

enter image description here

Upvotes: 1

a-burge
a-burge

Reputation: 1574

One way to achieve this:

=Month(date)+12*(Year(date)-2017)

The second part adds a count of 12 to each passing year, with the -2017 bit assuming your first date is in January 2017 (leaving you with 12x(2017-2017) = 12x0 = 0 for the first year.

Upvotes: 1

Kresimir L.
Kresimir L.

Reputation: 2441

You can achieve this with calculated column after you import your data with Power Query. First, please add this ranking column. Year&month num = YEAR(Sheet1[Date])&"-"&FORMAT(MONTH(Sheet1[Date]),"00")

Then please add this calculated column 

Index = COUNTROWS(
                FILTER(VALUES(Sheet1[Year&month num]),
                       Sheet1[Year&month num]<EARLIER(Sheet1[Year&month num])

))+1

Now it should only add new index if it is a new month.

enter image description here

Upvotes: 1

Related Questions