PowerBI Month Number Dax - return integers greater than 12

[Ryan4]

Hello,

I am attempting to tweak the Month() function in PowerBI to return integers greater than 12 after rolling into the new year. For example, Jan 2022 returns 1, Feb 2022 returns 2... Dec 2022 returns 12, Jan 2023 returns 13, and so on. Is this possible? I've searched all over and can't find any information on this.

I am calculating hour utilization by month, so the purpose of this is so I can define what the following month and the second following month are in relation to the current month, using the following:

NextMonth2 = If(Daily_Calendar_2[MonthNo] = MONTH(Today())+2, "Yes","No")

So since Nov 2022 = 11, if Jan 2023 =13 I can define as "Yes" and filter this into my consecutive month visual.

Thank you in advance!!

[v-jialluo-msft · Accepted solution]

Hi  @Ryan4 ,

Please follow these steps:
(1) Create a new Table

CALENDAR = ADDCOLUMNS(

CALENDAR(date(2022,1,1),date(2023,12,31)),

"YEAR",YEAR([Date]),

"MONTH",MONTH([Date]),

"YEAR-MONTH",(YEAR([Date])-2022)*12+MONTH([Date])

)

(2)Final output

Best Regards,
Gallen Luo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Ryan4 , try like

NextMonth2 = If(Daily_Calendar_2[MonthNo] = MONTH(Eomonth(Today(),2)), "Yes","No")

or

NextMonth2= if( eomonth([Date],0) >= eomonth(today(),0)+1  && eomonth([Date],0) <= eomonth(today(),2) ,"Yes","No")

[Ryan4]

Thanks @amitchandak! The first line worked, I am pulling in the Jan 2023 data, however it is also labeling Jan 2022 "Yes" even though this is not the NextMonth2. Is there any way I can include the year in the formula to differentiate it? Somehow read the data sequentially?

I have a YearMonth variable, e.g., 2022-01, 2022-02, ... but have unsuccessfully tried to incorporate this in the above code. Thanks again for the suggestions.