Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Ryan4
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.
 
Thank you in advance!!
1 ACCEPTED SOLUTION
v-jialluo-msft
Community Support
Community Support

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

vjialluomsft_0-1670220353090.png

 

 

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.

View solution in original post

3 REPLIES 3
v-jialluo-msft
Community Support
Community Support

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

vjialluomsft_0-1670220353090.png

 

 

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
Super User
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")

 

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.