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

thanks

Viral

[v-stephen-msft]

Hi @ViralPatel212 ,

I found out that no one has responded to your post when I was reviewing it, I took a look at your post and I understand that you want to custom sort in slicer.

What is the logical order of your quarters?

Do you need to create a relationship with the 'DIM Calendar (Ranking)' table for my your date column above?

Best Regards,

Stephen Tao

[ViralPatel212]

Helllo @v-stephen-msft 

Thank you for looking into my post. 

Ideally this is how i would want it to diplay: 

As the Quarter are created dynamically (based of the date) I would want the order to be like 

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

Q3 2024 = 16, Q2 2024=17, Q1 2024=18, Q4 2023 = 19, Q3 2023 = 20, Q2 2023 = 21, Q1 2023  =22

Thanks

[ViralPatel212]

Hello @foodd 

Apologies, i had created a sample file but i forgot to add the link

Same file can be access here: Sample File 

Thanks

Viral