Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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 da...
Need Help
  • alena2k's avatar
    alena2k
    8 years ago

    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.  

  • Anonymous's avatar
    Anonymous
    8 years ago

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

v-yulgu-msft's avatar
v-yulgu-msft
Microsoft Employee
8 years ago

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

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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