Forum Discussion
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?
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.
7 Replies
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.
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")?
Hi Anonymous,
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.
Now, you can add this new column into page level filter or any visual.
Best regards,
Yuliana Gu
