date data display

[kell]

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.

[collinsg]

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.

[v-cgao-msft]

Hi @kell,

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

[kell]

Hi Gao @v-cgao-msft 

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.

[v-cgao-msft]

Hi @kell ，

Create another new column in the calendar table to aid in sorting:

Week Range Sort = 'Calendar'[Year]*100 + 'Calendar'[Week]

and the select the [Week Range] sort by the [Week Range Sort].

Sort one column by another column in Power BI - Power BI | Microsoft Learn

Best Regards,
Gao
Community Support Team

[kell]

 Hi @v-cgao-msft ,

Thank you for your idea, But my mistake, I dont know why it did not work as notice:

Many thanks

[v-cgao-msft]

Hi @kell ,

This error means that  a WeekDisplay value that are bound to  two or more values in WeekDisplay Sort, like this:

Attach a PBIX file for reference.

Best Regards,
Gao
Community Support Team

[kell]

Thank you so much, @v-cgao-msft 

By the way,How can I fix this error ? I did the same your demo but cant work.

Many thanks

[v-cgao-msft]

Hi @kell ，

Yes, could you kindly share a sample file for a calendar table? Please be sure to erase any private information.

How to provide sample data in the Power BI Forum - Microsoft Fabric Community


Best Regards,
Gao
Community Support Team

[kell]

Hi @v-cgao-msft , @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 · Accepted solution]

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.

[kell]

Thank you guys so much, It did work.