Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Month number

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).
  • FreemanZ's avatar
    FreemanZ
    3 years ago

    hi Anonymous 

    try like:

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

3 Replies

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    3 years ago

    hi Anonymous 

    try like:

    NewColumn = 
    DATEDIFF([DATE], TODAY(), MONTH)
    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

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

      Column = -(DATEDIFF(TODAY(), [DATE], MONTH))
  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Hi Anonymous 

    please try

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