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.

     

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Anonymous , collinsg 

 

Thank you guys so much, I see the problem:

The weekdisplay doesnot match with weekdisplay sort, more than value in sort (202401 202402  for 29/5 - 05/05, it happened when new month 01/05. and it count new week when on sunday. How can I fix this guys? The weekdisplay and week did not match too, how can i fix this week start on monday?

 

Many thanks

 

  • collinsg's avatar
    collinsg
    Solution Sage
    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.

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Thank you guys so much, It did work.