Sorting Pivoted Calendar table Attribute and Values

[kulpowerbi]

I have a calendar tablet That I have transfermed and created another Pivoted table with attribute and values, so I can Apply filters like Annual, Quarterly, Monthly and daily with slicer.

Note: I don't want to use bookmark and hirachy to get he same result, due to some other limitations. So lookin for suggestion with only Pivoted calnder table.

Sorting Issue with Month-Year, Date(Numeric day only)-Month 

I am not able to sort MMM-YY. in this case, Since The Value Column is same for all the Values: for each type of time seris. I am looking for the help to sort Monthly and Daily, Without using tooltip (earliest date) option. Please see the screenshot for the issue:

My Calander and Calendar Script are these:
Calendar Code:

let
    // configurations start
     Today=Date.From(DateTime.LocalNow()), // today's date
    FromYear = 2018, // set the start year of the date dimension. dates start from 1st of January of this year
    //ToYear=2020, // set the end year of the date dimension. dates end at 31st of December of this year
    ToYear=Date.Year(DateTime.LocalNow()),
    ToMonth=Date.Month(DateTime.LocalNow())-1,
    ToDay=Date.Day(DateTime.LocalNow()),
    StartofFiscalYear=7, // set the month number that is start of the financial year. example; if fiscal year start is July, value is 7
    firstDayofWeek=Day.Monday, // set the week's start day, values: Day.Monday, Day, Sunday....
    // configuration end
    FromDate=#date(FromYear,1,1),
    //ToDate=#date(ToYear,ToMonth,ToDay),
    ToDate =Date.EndOfMonth(Date.AddMonths(Date.From(DateTime.LocalNow()), -1)),

// Expression.Error: We cannot apply operator - to types DateTime and Date.
// Details:
//     Operator=-
//     Left=9/1/2021 12:00:00 AM
//     Right=1/1/2018



    Source=List.Dates(
        FromDate,
        Duration.Days(ToDate-FromDate)+1,
        #duration(1,0,0,0)
    ),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
    #"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date]), Int64.Type),
    #"Inserted Start of Year" = Table.AddColumn(#"Inserted Year", "Start of Year", each Date.StartOfYear([Date]), type date),
    #"Inserted End of Year" = Table.AddColumn(#"Inserted Start of Year", "End of Year", each Date.EndOfYear([Date]), type date),
    #"Inserted Month" = Table.AddColumn(#"Inserted End of Year", "Month", each Date.Month([Date]), Int64.Type),
    #"Inserted Start of Month" = Table.AddColumn(#"Inserted Month", "Start of Month", each Date.StartOfMonth([Date]), type date),
    #"Inserted End of Month" = Table.AddColumn(#"Inserted Start of Month", "End of Month", each Date.EndOfMonth([Date]), type date),
    #"Inserted Days in Month" = Table.AddColumn(#"Inserted End of Month", "Days in Month", each Date.DaysInMonth([Date]), Int64.Type),
    #"Inserted Day" = Table.AddColumn(#"Inserted Days in Month", "Day", each Date.Day([Date]), Int64.Type),
    #"Inserted Day Name" = Table.AddColumn(#"Inserted Day", "Day Name", each Date.DayOfWeekName([Date]), type text),
    #"Inserted Day of Week" = Table.AddColumn(#"Inserted Day Name", "Day of Week", each Date.DayOfWeek([Date]), Int64.Type),
    #"Inserted Day of Year" = Table.AddColumn(#"Inserted Day of Week", "Day of Year", each Date.DayOfYear([Date]), Int64.Type),
    #"Inserted Month Name" = Table.AddColumn(#"Inserted Day of Year", "Month Name", each Date.MonthName([Date]), type text),
    #"Inserted Quarter" = Table.AddColumn(#"Inserted Month Name", "Quarter", each Date.QuarterOfYear([Date]), Int64.Type),
    #"Inserted Start of Quarter" = Table.AddColumn(#"Inserted Quarter", "Start of Quarter", each Date.StartOfQuarter([Date]), type date),
    #"Inserted End of Quarter" = Table.AddColumn(#"Inserted Start of Quarter", "End of Quarter", each Date.EndOfQuarter([Date]), type date),
    #"Inserted Week of Year" = Table.AddColumn(#"Inserted End of Quarter", "Week of Year", each Date.WeekOfYear([Date],firstDayofWeek), Int64.Type),
    #"Inserted Week of Month" = Table.AddColumn(#"Inserted Week of Year", "Week of Month", each Date.WeekOfMonth([Date],firstDayofWeek), Int64.Type),
    #"Inserted Start of Week" = Table.AddColumn(#"Inserted Week of Month", "Start of Week", each Date.StartOfWeek([Date],firstDayofWeek), type date),
    #"Inserted End of Week" = Table.AddColumn(#"Inserted Start of Week", "End of Week", each Date.EndOfWeek([Date],firstDayofWeek), type date),
    FiscalMonthBaseIndex=13-StartofFiscalYear,
    adjustedFiscalMonthBaseIndex=if(FiscalMonthBaseIndex>=12 or FiscalMonthBaseIndex<0) then 0 else FiscalMonthBaseIndex,
    #"Added Custom" = Table.AddColumn(#"Inserted End of Week", "FiscalBaseDate", each Date.AddMonths([Date],adjustedFiscalMonthBaseIndex)),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"FiscalBaseDate", type date}}),
    #"Inserted Year1" = Table.AddColumn(#"Changed Type1", "Year.1", each Date.Year([FiscalBaseDate]), Int64.Type),
    #"Renamed Columns1" = Table.RenameColumns(#"Inserted Year1",{{"Year.1", "Fiscal Year"}}),
    #"Inserted Quarter1" = Table.AddColumn(#"Renamed Columns1", "Quarter.1", each Date.QuarterOfYear([FiscalBaseDate]), Int64.Type),
    #"Renamed Columns2" = Table.RenameColumns(#"Inserted Quarter1",{{"Quarter.1", "Fiscal Quarter"}}),
    #"Inserted Month1" = Table.AddColumn(#"Renamed Columns2", "Month.1", each Date.Month([FiscalBaseDate]), Int64.Type),
    #"Renamed Columns3" = Table.RenameColumns(#"Inserted Month1",{{"Month.1", "Fiscal Month"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns3",{"FiscalBaseDate"}),
    #"Inserted Age" = Table.AddColumn(#"Removed Columns", "Age", each [Date]-Today, type duration),
    #"Extracted Days" = Table.TransformColumns(#"Inserted Age",{{"Age", Duration.Days, Int64.Type}}),
    #"Renamed Columns4" = Table.RenameColumns(#"Extracted Days",{{"Age", "Day Offset"}}),
    #"Added Custom1" = Table.AddColumn(#"Renamed Columns4", "Month Offset", each (([Year]-Date.Year(Today))*12)
+([Month]-Date.Month(Today))),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"Month Offset", Int64.Type}}),
    #"Added Custom2" = Table.AddColumn(#"Changed Type2", "Year Offset", each [Year]-Date.Year(Today)),
    #"Changed Type3" = Table.TransformColumnTypes(#"Added Custom2",{{"Year Offset", Int64.Type}}),
    #"Added Custom3" = Table.AddColumn(#"Changed Type3", "Quarter Offset", each (([Year]-Date.Year(Today))*4)
+([Quarter]-Date.QuarterOfYear(Today))),
    #"Changed Type4" = Table.TransformColumnTypes(#"Added Custom3",{{"Quarter Offset", Int64.Type}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type4", "Year", "Year - Copy"),
    #"Renamed Columns5" = Table.RenameColumns(#"Duplicated Column",{{"Year - Copy", "Year_New"}}),
    #"Changed Type5" = Table.TransformColumnTypes(#"Renamed Columns5",{{"Year_New", type text}}),
    #"Duplicated Column1" = Table.DuplicateColumn(#"Changed Type5", "Month Name", "Month Name - Copy"),
    #"Split Column by Position" = Table.SplitColumn(#"Duplicated Column1", "Month Name - Copy", Splitter.SplitTextByPositions({0, 3}, false), {"Month Name - Copy.1", "Month Name - Copy.2"}),
    #"Changed Type6" = Table.TransformColumnTypes(#"Split Column by Position",{{"Month Name - Copy.1", type text}, {"Month Name - Copy.2", type text}}),
    #"Merged Columns" = Table.CombineColumns(#"Changed Type6",{"Month Name - Copy.1", "Year_New"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"MonthYear"),
    #"Removed Columns1" = Table.RemoveColumns(#"Merged Columns",{"Month Name - Copy.2"}),
    #"Duplicated Column2" = Table.DuplicateColumn(#"Removed Columns1", "MonthYear", "MonthYear - Copy"),
    #"Inserted First Characters" = Table.AddColumn(#"Duplicated Column2", "First Characters", each Text.Start([#"MonthYear - Copy"], 3), type text),
    #"Renamed Columns6" = Table.RenameColumns(#"Inserted First Characters",{{"First Characters", "MonthShort"}}),
    #"Inserted Merged Column" = Table.AddColumn(#"Renamed Columns6", "QTR", each Text.Combine({"Q", Text.From([Quarter], "en-US")}), type text),
    #"Removed Columns2" = Table.RemoveColumns(#"Inserted Merged Column",{"MonthYear - Copy"}),
    #"Added Custom4" = Table.AddColumn(#"Removed Columns2", "YearQtr", each [QTR] &"-" & Text.End(
Number.ToText([Year]),2)),
    #"Added Custom5" = Table.AddColumn(#"Added Custom4", "YearMonth", each [MonthShort] & "-" & Text.End(
Number.ToText([Year]),2)),
    #"Added Conditional Column" = Table.AddColumn(#"Added Custom5", "Month Half", each if [Day] >= 15 then "0-15" else "16-31"),
    #"Duplicated Column3" = Table.DuplicateColumn(#"Added Conditional Column", "Date", "Date - Copy"),
    #"Renamed Columns7" = Table.RenameColumns(#"Duplicated Column3",{{"Date - Copy", "FromDate"}}),
    #"Duplicated Column4" = Table.DuplicateColumn(#"Renamed Columns7", "FromDate", "FromDate - Copy"),
    #"Renamed Columns8" = Table.RenameColumns(#"Duplicated Column4",{{"FromDate - Copy", "ToDate"}}),
    #"Duplicated Column5" = Table.DuplicateColumn(#"Renamed Columns8", "MonthShort", "MonthShort - Copy"),
    #"Renamed Columns9" = Table.RenameColumns(#"Duplicated Column5",{{"MonthShort - Copy", "FromMonth"}}),
    #"Duplicated Column6" = Table.DuplicateColumn(#"Renamed Columns9", "FromMonth", "FromMonth - Copy"),
    #"Renamed Columns10" = Table.RenameColumns(#"Duplicated Column6",{{"FromMonth - Copy", "ToMonth"}}),
    #"Duplicated Column7" = Table.DuplicateColumn(#"Renamed Columns10", "FromDate", "FromDate - Copy"),
    #"Renamed Columns11" = Table.RenameColumns(#"Duplicated Column7",{{"FromDate - Copy", "FromQtr"}}),
    #"Duplicated Column8" = Table.DuplicateColumn(#"Renamed Columns11", "FromQtr", "FromQtr - Copy"),
    #"Renamed Columns12" = Table.RenameColumns(#"Duplicated Column8",{{"FromQtr - Copy", "ToQtr"}}),
    #"Duplicated Column9" = Table.DuplicateColumn(#"Renamed Columns12", "Quarter", "Quarter - Copy"),
    #"Renamed Columns13" = Table.RenameColumns(#"Duplicated Column9",{{"Quarter - Copy", "FromQtrNo"}}),
    #"Duplicated Column10" = Table.DuplicateColumn(#"Renamed Columns13", "FromQtrNo", "FromQtrNo - Copy"),
    #"Renamed Columns14" = Table.RenameColumns(#"Duplicated Column10",{{"FromQtrNo - Copy", "ToQtrNo"}}),
    #"Duplicated Column11" = Table.DuplicateColumn(#"Renamed Columns14", "FromQtr", "FromQtr - Copy"),
    #"Renamed Columns15" = Table.RenameColumns(#"Duplicated Column11",{{"FromQtr - Copy", "FromMnth"}}),
    #"Duplicated Column12" = Table.DuplicateColumn(#"Renamed Columns15", "FromMnth", "FromMnth - Copy"),
    #"Renamed Columns16" = Table.RenameColumns(#"Duplicated Column12",{{"FromMnth - Copy", "ToMnth"}}),
    #"Added Custom6" = Table.AddColumn(#"Renamed Columns16", "MnthDay", each Text.PadStart(Text.From([Day]),2,"0") & " "& [MonthShort]),
    #"Added Custom7" = Table.AddColumn(#"Added Custom6", "MonthYear_Num", each Number.ToText([Month]) & Number.ToText([Year])),
    #"Changed Type7" = Table.TransformColumnTypes(#"Added Custom7",{{"MonthYear_Num", Int64.Type}}),
    #"Added Custom8" = Table.AddColumn(#"Changed Type7", "Month_Year_N", each [Year]*100 + [Month])
in
    #"Added Custom8"

Calendar Pivot Code:

let
    Source = #"Calender Table",
    #"Added Custom" = Table.AddColumn(Source, "Year-QTR", each Text.End(
Number.ToText([Year]),2) & 
"-"&[QTR]),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Date", "Year", "YearMonth", "MnthDay", "Year-QTR"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Duplicates", {"Date"}, "Attribute", "Value"),
    #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Columns","Year","Annual",Replacer.ReplaceText,{"Attribute"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","Annual-QTR","Quarterly",Replacer.ReplaceText,{"Attribute"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","MnthDay","Daily",Replacer.ReplaceText,{"Attribute"}),
    #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","AnnualMonth","Monthly",Replacer.ReplaceText,{"Attribute"}),
    #"Added Conditional Column" = Table.AddColumn(#"Replaced Value3", "Custom", each if [Attribute] = "Annual" then 1 else if [Attribute] = "Quarterly" then 2 else if [Attribute] = "Monthly" then 3 else if [Attribute] = "Daily" then 4 else null),
    #"Renamed Columns" = Table.RenameColumns(#"Added Conditional Column",{{"Custom", "Attribute_Sort"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Attribute_Sort", Int64.Type}})
in
    #"Changed Type"

Thanks in advance!

[v-yangliu-msft · Accepted solution]

Hi  @kulpowerbi ,

Here are the steps you can follow：

For sorting, the default is to sort by the first letter order.

Can you accept the following format:

1. Create calculated column.

Column =
SWITCH(
    TRUE(),
    'Calendar Pivot'[Attribute]="Annual",'Calendar Pivot'[Value],
    'Calendar Pivot'[Attribute]="Quarterly",'Calendar Pivot'[Value],
    'Calendar Pivot'[Attribute]="Daily",FORMAT('Calendar Pivot'[Date],"mm/dd"),
    'Calendar Pivot'[Attribute]="Monthly",FORMAT('Calendar Pivot'[Date],"yy/mm")
    )

2. Result:

When selecting "Daily". The result is:

When "Monthly" is selected, the result is:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly