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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Absalon29
Helper III
Helper III

sorting a date table chronologically

I created a date table to do time intelligence calculations on my models. My excel models all work off a monthly valuation date, the last working day of the month. When I insert month/year from my date model (see below) it can only appear to sort by alphabetical month or by year,  I can't understand how to sort this chronologically. Any suggestions?

 

Filter function.JPG

2 ACCEPTED SOLUTIONS

Is that what you want to see as result? If yes, simply add to your Date Dim couple column:Month & Year = FORMAT([Date], "MMM" & " " & "YYYY") and Sort = FORMAT([Date], "YYYYMM") ; define "Sort by Column" accordingly it will give you correct sequence.  

Capture.PNG

Capture2.PNG

View solution in original post

You are a genius! Thanks so much, that worked exactly the way I wanted it. 

View solution in original post

7 REPLIES 7
v-yulgu-msft
Employee
Employee

Hi @Absalon29,

 

When we use FORMAT function to make date display in "MMM YYYY" format, its data type will be changed to Text automatically. That is why it will be sorted based on alphabeti. To sort it chronologically, you should have a proper date column.

 

In your date table, please add some calculated columns as below:

Last working day per month =
CALCULATE (
    MAX ( 'calendar'[Date] ),
    FILTER (
        ALL ( 'calendar' ),
        'calendar'[Date].[Year] = EARLIER ( 'calendar'[Date].[Year] )
            && 'calendar'[Date].[MonthNo] = EARLIER ( 'calendar'[Date].[MonthNo] )
            && WEEKDAY ( 'calendar'[Date], 2 ) = 5
    )
)

 

Set this new column with "MMMM yyyy" format.

1.PNG

 

Now, you can add this new column into page level filter or any visual.

 

Best regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

You are a genius! Thanks so much, that worked exactly the way I wanted it. 

Greg_Deckler
Super User
Super User

You would likely need to have a proper date column and you could use "Sort By" to sort your original month/year column by the date column.


@ 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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

I have a proper date column but using it  as a page filter gives me a list of every day of every month and I only have one data point per month (the valuation date) that I want to capture. When I put the date column into the filter I therefore get a whole load of unnecessary dates. As an alternative I have tried using Month from my date table (Month Year) but that filter doesn't appear to filter chronologically. 

Do you have a Sort column for your (Month Year) column? Something like FORMAT([your date key],"YYYYMM")?

I think that you have misunderstood. I have a date table with date, day, dayofmonth, dayofweek, month, monthofyear,ordinaldate, quarter, quarterfoyear, weekending, year. When I put the date column into the page level filter it gives me every day of every month of every year, but I only want one data point each month (my valuation date which is the last working day of each month). As an alternative I tried using Month = FORMAT([Date]; "MMM") & " " & [Year] but when that goes into the page level filter, it does not sort chronologically. I am trying to figure out how I can either remove all the dates that arenn't a valuation date in a month so that it can be used a page fillter or sort the Month column in the page level filter chronologically

Is that what you want to see as result? If yes, simply add to your Date Dim couple column:Month & Year = FORMAT([Date], "MMM" & " " & "YYYY") and Sort = FORMAT([Date], "YYYYMM") ; define "Sort by Column" accordingly it will give you correct sequence.  

Capture.PNG

Capture2.PNG

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.