Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi
I am looking to get the month number and the year when referening a date field
However, when trying to use Month() or Format () it doesnt allow me to use the Date field
I want to get the month number and year from the data column - which will allow me to create a reference of Year * 12 + month number
Any Suggestions ?
customer | due-date | Month Number | Year | Reference | Calculation |
1001 | 28/02/2023 | 2 | 2023 | 24278 | =(D2*12)+C2 |
1001 | 31/03/2023 | 3 | 2023 | 24279 | |
1001 | 31/03/2022 | 3 | 2022 | 24267 | |
1001 | 30/04/2022 | 4 | 2022 | 24268 | |
1001 | 30/06/2022 | 6 | 2022 | 24270 | |
1001 | 31/08/2022 | 8 | 2022 | 24272 |
Thank you
I regularly use the following DAX from SQLBI to create a Date Table. Highlighted in the code are the 2 items you are looking for:
- Year
- Month Number
Hope this is helpful to you.
Regards,
Nathan
User | Count |
---|---|
22 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
25 | |
12 | |
11 | |
7 | |
6 |