Forum Discussion

SRL's avatar
SRL
New Member
2 years ago
Solved

Fiscal Month

Hello,

I have created a fiscal calendar using the below and then created a relationship with it to the sales date. 

Fiscal Year Table = CALENDARAUTO(10)
Fiscal Month No. = Month(EDATE('Fiscal Year Table'[Date],-10))
 
When I use a matrix or graph the months show up in the correct fiscal year however they show up in calendar order not fiscal order. I would like November and December to be at the top of the fiscal year.  Is that possible?

 

 
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi SRL ,

    I've created a simple example that can be sorted by specific dates in a matrix.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XdHLDcQgDATQXjhHMh4+hlqi9N/GgomWCdcn7BngvgMikqhouIKW8Fx/KVPqFohNMZY+ZUPyIWOYJ9qGLG1AZ1AMoeTiOWDA2a6uLpUlxaOeeRs0llkHtKj5xZFYfCpu6euMsngYL9IoesYNS948k71P3b90tlAIfF39mn1i8/tNsC15Pd+Ye34=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [date = _t, value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type date}, {"value", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Date.Month([date])),
    #"Added Conditional Column" = Table.AddColumn(#"Added Custom", "Custom.1", each if [Custom] = 11 then 1 else if [Custom] = 12 then 2 else if [Custom] = 1 then 3 else if [Custom] = 2 then 4 else if [Custom] = 3 then 5 else if [Custom] = 4 then 6 else if [Custom] = 5 then 7 else if [Custom] = 6 then 8 else if [Custom] = 7 then 9 else if [Custom] = 8 then 10 else if [Custom] = 9 then 11 else if [Custom] = 10 then 12 else 0)
in
    #"Added Conditional Column"

    I have also found a similar post, please refer to it to see id it helps you.

    Solved: Power BI Matrix Date Column Sorting - Microsoft Fabric Community

     

     

     

    How to Get Your Question Answered Quickly 

     

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

     

    Best Regards
    Community Support Team _ Rongtie

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

     

     

     

     

3 Replies

    • SRL's avatar
      SRL
      New Member
      2 years ago

      Hi Greg 

       

      Thanks for the reply. When I go to sort by Fiscal Month number I am getting a circular dependcy. Or when I use a measurement they cannot find a single value in my date column to pull out the month. I do not have a column currently with Months in it. Only a sale date column.   Is there another step I need?

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi SRL ,

    I've created a simple example that can be sorted by specific dates in a matrix.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XdHLDcQgDATQXjhHMh4+hlqi9N/GgomWCdcn7BngvgMikqhouIKW8Fx/KVPqFohNMZY+ZUPyIWOYJ9qGLG1AZ1AMoeTiOWDA2a6uLpUlxaOeeRs0llkHtKj5xZFYfCpu6euMsngYL9IoesYNS948k71P3b90tlAIfF39mn1i8/tNsC15Pd+Ye34=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [date = _t, value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type date}, {"value", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Date.Month([date])),
    #"Added Conditional Column" = Table.AddColumn(#"Added Custom", "Custom.1", each if [Custom] = 11 then 1 else if [Custom] = 12 then 2 else if [Custom] = 1 then 3 else if [Custom] = 2 then 4 else if [Custom] = 3 then 5 else if [Custom] = 4 then 6 else if [Custom] = 5 then 7 else if [Custom] = 6 then 8 else if [Custom] = 7 then 9 else if [Custom] = 8 then 10 else if [Custom] = 9 then 11 else if [Custom] = 10 then 12 else 0)
in
    #"Added Conditional Column"

    I have also found a similar post, please refer to it to see id it helps you.

    Solved: Power BI Matrix Date Column Sorting - Microsoft Fabric Community

     

     

     

    How to Get Your Question Answered Quickly 

     

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

     

    Best Regards
    Community Support Team _ Rongtie

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