Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
kell
Helper I
Helper I

date data display

Hi everyone,

 

I have data like this, it show only number of week: 

kell_2-1718536236019.png

But how can i display like this: from day to day as picture below:

kell_0-1718536109802.png

 

Thank you so much.

Best regards.

 

1 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...

collinsg_2-1718870973481.png

Hope this helps.

 

View solution in original post

11 REPLIES 11
collinsg
Super User
Super User

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
Community Support
Community Support

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

vcgaomsft_1-1718588240968.png

Output:

vcgaomsft_0-1718588214081.png

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

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.

kell_0-1718590236917.png

 

Please help.

Many thanks Gao.

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

 Hi @v-cgao-msft ,

 

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

kell_0-1718591903494.png

Many thanks

Hi @kell ,

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

vcgaomsft_0-1718593172426.png

Attach a PBIX file for reference.

 

Best Regards,
Gao

Community Support Team

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.

kell_0-1718594735782.png

 

 

Many thanks

 

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

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

kell_0-1718706964369.png

 

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...

collinsg_2-1718870973481.png

Hope this helps.

 

Thank you guys so much, It did work. 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors