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

[freakinaditya]

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

[v-eqin-msft · Accepted solution]

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:

    

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]

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

[freakinaditya]

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

[DebbieE]

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