date data display

[Syndicate_Admin]

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.

[Syndicate_Admin]

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

[Syndicate_Admin]

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.

[Syndicate_Admin]

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

[Syndicate_Admin]

 Hi @v-cgao-msft ,

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

Many thanks

[Syndicate_Admin]

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

[Syndicate_Admin]

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.