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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
freakinaditya
Helper II
Helper II

How to create a month column having specific range of dates ?

Hi Community,

 

I have a user requirement such that they wanted to see the data in months but for them month definition is different.

Suppose the data is from - 21st March to 20th April - It will be considered as "April" Month similarly,
 

21st April to 20th May - May
21st May to 20th June - June
21st June to 20th July - July
 ,

,

,

21st Feb to 20th March - March

Dates in month should start from 21st and ends on 20th. for every year

 

How can we achieve it in power bi dax?


Thanks in advance!!

1 ACCEPTED SOLUTION
v-eqin-msft
Community Support
Community Support

Hi @freakinaditya ,

 

It seems that you want to add a Fiscal Year and Month, please try:

Fiscal Year = IF(DAY([Date])>20 && MONTH([Date])=12, YEAR([Date])+1,YEAR([Date])) 
Fiscal Month = 
var _month=IF(DAY([Date])<=20,MONTH([Date]), MONTH([Date])+1)
return FORMAT( DATE(YEAR([Date]),_month,1),"mmmm")

Output:

Eyelyn9_0-1646098991543.png    Eyelyn9_1-1646099106425.png

 

 

Best Regards,
Eyelyn Qin
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

4 REPLIES 4
v-eqin-msft
Community Support
Community Support

Hi @freakinaditya ,

 

It seems that you want to add a Fiscal Year and Month, please try:

Fiscal Year = IF(DAY([Date])>20 && MONTH([Date])=12, YEAR([Date])+1,YEAR([Date])) 
Fiscal Month = 
var _month=IF(DAY([Date])<=20,MONTH([Date]), MONTH([Date])+1)
return FORMAT( DATE(YEAR([Date]),_month,1),"mmmm")

Output:

Eyelyn9_0-1646098991543.png    Eyelyn9_1-1646099106425.png

 

 

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

DebbieE
Community Champion
Community Champion

I would definitely have a month column already set up in my date table that I could work with I think so

Date                       Month

21/04/2021            May

22/04/2021            May

...

01/05/2021            May

 

I think thats definitely the first thing I would do in this situation, Then you can use that column in your DAX

@DebbieE , Sorry I didn't get you.
I have already created a date table but I need that month column (as per requirement) as an additional column.

Ah I see. Ive been having a look at it seems like Power Query M Might be the best place for this. Im sure there are lots of better ways to do this but in M I

Duplicated the date column and changed to Day number of month just using one of the build in date functions

 

then I created a custom column using this new column

if List.Contains({21,22,23,24,25,26,27,28,29,30,31},[DayNumberOfMonth])
then Date.StartOfMonth(Date.AddMonths([Date], 1))
else [Date]

 

And then I changed it to Month

 

This seemed to be ok but i dont know quite how you would do the same thing in DAX. I would personally prefer it to be a column before it hit DAX so its compressed as a column

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.