cancel
Showing results for
Did you mean:
Regular Visitor

## PowerBI Month Number Dax - return integers greater than 12

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.

1 ACCEPTED SOLUTION
Community Support

Hi  @Ryan4 ,

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

3 REPLIES 3
Community Support

Hi  @Ryan4 ,

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

Super User

@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")

!! Microsoft Fabric !!
Microsoft Power BI Learning Resources, 2023 !!
Learn Power BI - Full Course with Dec-2022, with Window, Index, Offset, 100+ Topics !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Regular Visitor

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.