cancel
Showing results for 
Search instead for 
Did you mean: 
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
Microsoft
Microsoft

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!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

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
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

Power BI Dev Camp Session 22 without aka link and time 768x460.jpg

Check it Out!

Watch Session 22 Ted's Dev Camp along with past sessions!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!

Top Kudoed Authors