topic Re: Month number in DAX Commands and Tips

Month number

Anonymous — Mon, 30 Jan 2023 14:55:56 GMT

Hello, I need to have in PowerBI a column which will display number of month in callendar.

At this moment I have an Callendar table created by

CALENDAR = CALENDAR(DATE(2018.,1.,1),EOMONTH(Today(),0)) function.

Now I need a cloumn which will display number of month started from courrent month, for eg.:

January 2023 (this month) - 0

December 2022 - 1

November 2022 - 2

October 2022 - 3

September 2022 - 4

etc.

It is crucial to have data alligned with every future months and updated with past.

Every next current month will get "0", and previous "1", and next prev. one "2" etc. till end of created callendar table (the numbers are not from 1-12 range).

Re: Month number

FreemanZ — Mon, 30 Jan 2023 15:06:52 GMT

hi Anonymous

try like:

NewColumn =

DATEDIFF([DATE], TODAY(), MONTH)

Re: Month number

tamerj1 — Mon, 30 Jan 2023 15:10:01 GMT

Hi Anonymous

please try

Number of Month =
RANKX ( 'CALENDAR', FORMAT ( 'CALENDAR'[Date], "YYYYMM" ),, Asc, DENSE ) - 1

Re: Month number

Anonymous — Mon, 30 Jan 2023 15:14:16 GMT

Thanks it works. I needed only to add "-" before statement to achieve exactly what I want.

Column = -(DATEDIFF(TODAY(), [DATE], MONTH))