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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Calendar table with "outdated" months

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.

1 ACCEPTED SOLUTION
Community Champion

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!

4 REPLIES 4
Frequent Visitor

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.

Community Champion

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!

Super User

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

Super User

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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

## Helpful resources

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors