Dynamically create months-year in a table

[ViralPatel212]

Hello Community,

I have a Date filter table where i have created values based of parameter:

Most recent being the full previous month and then showing each month prior to that

The calculation is correct however my sort order is incorrect: its starts from Most Recent (Current calender month Jun24), May 23 Jun 23 July 23 etc which is wrong.  

MY sort order should be Most recent = 1, May 24 = 2, Apr 24 = 3, Mar 24 = 4, Feb 24 = 5, Jan 24 = 6, Dec 23 = 7, Nov 23 = 8, Oct 23 = 9, Sept 23 = 10, Aug 23 = 11, Jul 23 = 12, Jun 23 = 13, May 23 = 14 etc 

Same file can be access here: Sample File 

DIM Date Filter = 
VAR _latest = MAX('DIM Calendar (Ranking)'[Date])
VAR _oldest = DATE(2023, 5, 22) -- Specific start date
VAR _previousYearStart = DATE(YEAR(_latest) - 1, 1, 1)
VAR _previousYearEnd = DATE(YEAR(_latest) - 1, 12, 31)
VAR _startOfPreviousMonth = EOMONTH(_latest, -2) + 1
VAR _endOfPreviousMonth = EOMONTH(_latest, -1)
VAR _endOfPreviousMonth2 = EOMONTH(_latest, -2)
VAR _startDate = DATE(YEAR(_oldest), MONTH(_oldest), 1)

RETURN
UNION(
    ADDCOLUMNS(
        CALENDAR(_startOfPreviousMonth, _endOfPreviousMonth),
        "Date Periods", "Most Recent Month",
        "Order", 1
    ),
    ADDCOLUMNS(
        CALENDAR(_startDate, _endOfPreviousMonth2),
        "Date Periods", FORMAT([Date], "MMM YY"),
        "Order", DATEDIFF(_startDate, EOMONTH([Date], 0), MONTH) + 2
    ),
    ADDCOLUMNS(
        CALENDAR(DATE(YEAR(_latest), 1, 1), _latest),
        "Date Periods","YTD",
        "Order", DATEDIFF(_startDate, _latest, MONTH) + 3
    ),
    -- Dynamically generate quarters for the current year
    ADDCOLUMNS(
        FILTER(
            CALENDAR(DATE(YEAR(_latest), 1, 1), _latest),
            MONTH([Date]) <= MONTH(_latest)
        ),
        "Date Periods", "Q" & FORMAT([Date], "Q YYYY"),
        "Order", DATEDIFF(_startDate, _latest, MONTH) + 4 + QUARTER([Date]) - 1
    ),
    -- Dynamically generate quarters for the previous year
    ADDCOLUMNS(
        FILTER(
            CALENDAR(_previousYearStart, _previousYearEnd),
            MONTH([Date]) <= 12 &&
            YEAR([Date]) = YEAR(_previousYearStart)
        ),
        "Date Periods", "Q" & FORMAT([Date], "Q YYYY"),
        "Order", DATEDIFF(_startDate, _latest, MONTH) + 8 + QUARTER([Date]) - 1
    ),
    -- Additional hardcoded years or custom periods can be added here
    ADDCOLUMNS(
        CALENDAR(DATE(2023, 1, 1), DATE(2023, 12, 31)),
        "Date Periods", "2023",
        "Order", DATEDIFF(_startDate, _latest, MONTH) + 12
    ),
    -- Add custom periods if needed
    ADDCOLUMNS(
        CALENDAR(_oldest, _latest),
        "Date Periods", "Custom",
        "Order", DATEDIFF(_startDate, _latest, MONTH) + 13
    )
)

[v-jtian-msft · Accepted solution]

Hi,@ViralPatel212 I am glad to help you.
Hello,@amitchandak ,thanks for your concern about this issue.

Your answer is excellent!
And I would like to share some additional solutions below.
Your original code is very good, I modified the Order column's sorting logic to achieve the order you need, sorting from closest to farthest in time.

DIM Date Filter_change = 
VAR _latest = MAX('DIM Calendar (Ranking)'[Date])
VAR _oldest = DATE(2023, 5, 22) -- Specific start date
VAR _previousYearStart = DATE(YEAR(_latest) - 1, 1, 1)
VAR _previousYearEnd = DATE(YEAR(_latest) - 1, 12, 31)
VAR _startOfPreviousMonth = EOMONTH(_latest, -2) + 1
VAR _endOfPreviousMonth = EOMONTH(_latest, -1)
VAR _endOfPreviousMonth2 = EOMONTH(_latest, -2)
VAR _startDate = DATE(YEAR(_oldest), MONTH(_oldest), 1)
RETURN
UNION(
    ADDCOLUMNS(
        CALENDAR(_startOfPreviousMonth, _endOfPreviousMonth),
        "Date Periods", "Most Recent Month",
        "Order", 1
    ),
    ADDCOLUMNS(
        CALENDAR(_startDate, _endOfPreviousMonth2),
        "Date Periods", FORMAT([Date], "MMM YY"),
        "Order", 
        SWITCH(
            TRUE(),
            FORMAT([Date], "MMM YY") = "May 24", 2,
            FORMAT([Date], "MMM YY") = "Apr 24", 3,
            FORMAT([Date], "MMM YY") = "Mar 24", 4,
            FORMAT([Date], "MMM YY") = "Feb 24", 5,
            FORMAT([Date], "MMM YY") = "Jan 24", 6,
            FORMAT([Date], "MMM YY") = "Dec 23", 7,
            FORMAT([Date], "MMM YY") = "Nov 23", 8,
            FORMAT([Date], "MMM YY") = "Oct 23", 9,
            FORMAT([Date], "MMM YY") = "Sep 23", 10,
            FORMAT([Date], "MMM YY") = "Aug 23", 11,
            FORMAT([Date], "MMM YY") = "Jul 23", 12,
            FORMAT([Date], "MMM YY") = "Jun 23", 13,
            FORMAT([Date], "MMM YY") = "May 23", 14,
            DATEDIFF(_startDate, EOMONTH([Date], 0), MONTH) + 15
        )
    ),
    ADDCOLUMNS(
        CALENDAR(DATE(YEAR(_latest), 1, 1), _latest),
        "Date Periods","YTD",
        "Order", DATEDIFF(_startDate, _latest, MONTH) + 16
    ),
    -- Dynamically generate quarters for the current year
    ADDCOLUMNS(
        FILTER(
            CALENDAR(DATE(YEAR(_latest), 1, 1), _latest),
            MONTH([Date]) <= MONTH(_latest)
        ),
        "Date Periods", "Q" & FORMAT([Date], "Q YYYY"),
        "Order", DATEDIFF(_startDate, _latest, MONTH) + 17 + QUARTER([Date]) - 1
    ),
    -- Dynamically generate quarters for the previous year
    ADDCOLUMNS(
        FILTER(
            CALENDAR(_previousYearStart, _previousYearEnd),
            MONTH([Date]) <= 12 &&
            YEAR([Date]) = YEAR(_previousYearStart)
        ),
        "Date Periods", "Q" & FORMAT([Date], "Q YYYY"),
        "Order", DATEDIFF(_startDate, _latest, MONTH) + 21 + QUARTER([Date]) - 1
    ),
    -- Additional hardcoded years or custom periods can be added here
    ADDCOLUMNS(
        CALENDAR(DATE(2023, 1, 1), DATE(2023, 12, 31)),
        "Date Periods", "2023",
        "Order", DATEDIFF(_startDate, _latest, MONTH) + 25
    ),
    -- Add custom periods if needed
    ADDCOLUMNS(
        CALENDAR(_oldest, _latest),
        "Date Periods", "Custom",
        "Order", DATEDIFF(_startDate, _latest, MONTH) + 26
    )
)

 Sort the fields in the table by [Order].

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ViralPatel212]

Hello @v-jtian-msft 

Thank you for that! that works perfectly! 

[amitchandak]

@ViralPatel212 , In date table, have these two columns

Month Type = Switch( True(),
eomonth([Date],0) = eomonth(Today(),-1*month(Today())),"Last year Last Month" ,
eomonth([Date],0) = eomonth(Today(),-1),"Last Month" ,
eomonth([Date],0)= eomonth(Today(),0),"Most Recent Month" ,
Format([Date],"MMM-YYYY")
)

Sort it on a column  = Year([Date])*100 + Month([Date])

[ViralPatel212]

hi @amitchandak 

Thanks for the solution, however that did not work.  I have a sample file here if you could try on this: 

Same file can be access here: Sample File