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.

     

11 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous,

    Please use a calendar table and create a new calculated column like:

    Week Range = 
VAR __WeekStartDate = 'Date'[Date] - WEEKDAY('Date'[Date], 2) + 1
VAR __WeekEndDate = 'Date'[Date] + 7 - WEEKDAY('Date'[Date], 2)
VAR __result =  FORMAT(__WeekStartDate, "dd/MM") & "-" & FORMAT(__WeekEndDate, "dd/MM")
RETURN
__result

    Output:

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Gao Anonymous 

      Thank you so much for your help. I got this result but however i cant sort it out even i click in sort chart but it did not work as yours.

       

      Please help.

      Many thanks Gao.

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