Calendar table with "outdated" months

[ChrisCollao]

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.

[ChrisCollao]

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.

[harshnathani · Accepted 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!

[parry2k]

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

[Greg_Deckler]

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