cancel
Showing results for
Did you mean:
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?

1 ACCEPTED SOLUTION
Community Support

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:

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.

4 REPLIES 4
Community Support

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:

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.

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

Helper II

@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.

Super User

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])
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

Announcements

#### Power BI September 2023 Update

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

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### 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!

#### 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
Top Kudoed Authors