Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

date data display

Hi everyone,   I have data like this, it show only number of week:  But how can i display like this: from day to day as picture below:   Thank you so much. Best regards.  
Need Help
  • collinsg's avatar
    collinsg
    2 years ago

    Hello kell,

    In your example "week" is week of month and "week 2" is month of year. "WeekDisplay Sort" concatenates year and week of month - avoid using it for sorting because (e.g.) week 1 of January and week 1 of February would have the same "WeekDisplay Sort" value. Instead, use year and week of year for sorting. Here is an example, and in the example I take Monday as the start of the week.

    let
      Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tcq5DQAgDASwXaiRAiEfsyD2XwMKxKVz4bUKKwmxlF2vLdmTI3nCo8Gd9BdwgAIqaKCD8bgP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
      #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
      #"Added Week" = Table.AddColumn(#"Changed Type", "Week", each Date.ToText( Date.StartOfWeek([Date], Day.Monday), [Format="dd/MM"] ) & "-" & Date.ToText( Date.EndOfWeek([Date], Day.Monday), [Format="dd/MM"] ), type text),
      #"Added Week Number" = Table.AddColumn(#"Added Week", "Week Number", each Date.WeekOfYear( [Date], Day.Monday ), Int64.Type),
      #"Added Week Sort" = Table.AddColumn(#"Added Week Number", "Week Sort", each Number.From( Date.ToText([Date],[Format="yyyy"]) & Text.End("00" & Text.From([Week Number]),2)), Int64.Type )
    in
     #"Added Week Sort"

    This gives the result...

    Hope this helps.

     

collinsg's avatar
collinsg
Icon for Solution Sage rankSolution Sage
2 years ago

Good day kell,

If your date range includes more than one year, both "Week" and "Week Number" will need to include year in order to have a useable sort order. For example week number 1 of 2024 is "31/12-06/01" whereas week number 1 of 2023 was "01/01-07/01" - making a sort by week number alone ambiguous. This would lead to the error you describe "There is more than one value...".

 

If you wish to have a solution in Power Query the following approach may be used. It is similar to the DAX solution by v-cago-msft.

  1. Add a "Week" column.
  2. Add a "Week Number" column.
  3. Load to the data model.
  4. Either a) in Excel Power Pivot sort "Week" by "Week Number" or b) in Power BI, Column Tools, sort "Week" by "Week Number".
let
  Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc67DYAwFMXQXVIj8a4Jv1mi7L8GKbHkxt0Zo1X2FUVvc1uL9/B27+m9vLf38b7alNeqWBWrYlWsilWxKlbFKqzCKqzCKqzCKn6q+QE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
  #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
  #"Added Week" = Table.AddColumn(#"Changed Type", "Week", each Date.ToText( Date.StartOfWeek([Date], Day.Sunday), [Format="dd/MM"] ) & "-" & Date.ToText( Date.EndOfWeek([Date], Day.Sunday), [Format="dd/MM"] ), type text),
  #"Added Week Number" = Table.AddColumn(#"Added Week", "Week Number", each Date.WeekOfYear( [Date], Day.Sunday ), Int64.Type)
in
  #"Added Week Number"

 Hope this helps.