cancel
Showing results for 
Search instead for 
Did you mean: 
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
Super User
Super User

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
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors