Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hi everyone,
Sorry if the subject is not correctly explained, but, basically I would like to ask if there is a way to create a calendar table, where I can add a month column, but, with the conditions that every month is from 26 to 25, instead of 1 to 30.
Explaining this with an example: june must be from may 26th to june 25th. And this for each month.
Month | Date range |
jan | dec-26 to jan-25 |
feb | jan-26 to feb-25 |
mar | feb-26 to mar-25 |
... | ... |
This is to let users filter by month, but with the date ranges ready.
Do you have any ideas?
Thanks a lot.
Solved! Go to Solution.
Hi @ChrisCollao ,
I assume you have a Date Table
Create a Calculate Column
New Month = IF (DAY(Dates[Date]) > 25, MONTH(Dates[Date]) + 1, Month(Dates[Date]))
Create the second Column for Month Names
Month Name =
VAR n = Dates[New Month]
RETURN
IF(
n > 0 && n < 13 ,
-- THEN --
FORMAT(DATE(YEAR(Dates[Date]), n , DAY(Dates[Date]) ) , "MMM") ,
-- ELSE --
"Other"
)
Regards,
Harsh Nathani
Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!
Thank you all for the solutions. They were all right. But I accepted the more simple and efficient for my case.
Best regards to all of you and thanks for the support.
Hi @ChrisCollao ,
I assume you have a Date Table
Create a Calculate Column
New Month = IF (DAY(Dates[Date]) > 25, MONTH(Dates[Date]) + 1, Month(Dates[Date]))
Create the second Column for Month Names
Month Name =
VAR n = Dates[New Month]
RETURN
IF(
n > 0 && n < 13 ,
-- THEN --
FORMAT(DATE(YEAR(Dates[Date]), n , DAY(Dates[Date]) ) , "MMM") ,
-- ELSE --
"Other"
)
Regards,
Harsh Nathani
Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!
@ChrisCollao already great solutions are provided but I will take a different approach and use less known/used DAX function
add a new column in your table
Custom Month =
VAR __newMonth = IF (
DAY ( 'Date'[Date] ) >= 26, EOMONTH ( 'Date'[Date], 1 ), 'Date'[Date] )
RETURN
FORMAT ( __newMonth, "MMM, YYYY" )
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@ChrisCollao - Perhaps try something along the lines of this:
Month =
VAR __Months =
{
("jan",1),("feb",2),("mar",3),("apr",4),("may",5),("jun",6),("jul",7),("aug",8),("sep",9),("oct",10),("nov",11),("dec",12)
}
VAR __Month = MONTH([Date])
VAR __Day = DAY([Date])
VAR __ReturnMonth =
SWITCH(TRUE(),
__Day >=26 && __Month = 12,1,
__Day >= 26,__Month+1,
__Month
)
RETURN
MAXX(FILTER(__Months,[Value2]=__ReturnMonth),[Value1])
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
126 | |
78 | |
77 | |
60 | |
52 |
User | Count |
---|---|
165 | |
86 | |
68 | |
68 | |
58 |