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

Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more

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
Anonymous
Not applicable

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
Anonymous
Not applicable

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
PBIApril_Carousel

Power BI Monthly Update - April 2025

Check out the April 2025 Power BI update to learn about new features.

Notebook Gallery Carousel1

NEW! Community Notebooks Gallery

Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.

April2025 Carousel

Fabric Community Update - April 2025

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