Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi -
please help with the dax formula to create combination of week and date of every monday.
basically we want to create weekly report and if we use week numbers, it will be difficult to understand in which range date of the charts, hence we want to put the date as of monday date.
I use [weekday]-2, so everything under 1-7 should follow date no.1 (monday's date).
the achievement will be W-MM/DD, sample as below
Thanks
Solved! Go to Solution.
Hi @rezaaditia ,
I hope you have a calendar ( date ) table in your model.
Do the following
1. Create a calculated column called Week
Hi @rezaaditia ,
Try the following
1. Add another column
YearWeek = Year(Table[Date]) * 100 + Table[WeekNum]
( Replace Table by your tablename)
2. Using the modelling tab set the Sort order for WeekName to YearWeek.
Cheers
CheenuSing
Hi @rezaaditia ,
You should not add the YearWeek in the axis name.
what you should do is select the Calculated column WeekName in the Fields pane.
Go to the modelling pane
From the sort by column set it to YearWeek.
Cheers
CheenuSing
hi @rezaaditia ,
Use this formula,
Hi @rezaaditia
Please see the below Query Editor solution.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XdGxDYNAEEXBXogtcbtnzKcWRP9tQOJgCF820jvPZdQ69rVHHcv1ebLNaX7NzfyZuxnzIGuYqkpVqSpVpapUlapSVapaVatqVa2qVbWqVtWqWlWrmqrm6+CTcWgcGofGoXFoHBqHxqFxaBwah8ahceg/rxs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "week", each Date.WeekOfYear([Date]), Int64.Type), #"Grouped Rows" = Table.Group(#"Added Custom", {"week"}, {{"weekStart", each List.Min([Date]), type date}}), #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Week Day", each "W-" & Text.PadStart( Number.ToText( Date.Month( [weekStart] ) ), 2, "0" ) & "/" & Text.PadStart( Number.ToText( Date.Day( [weekStart] ) ), 2, "0" ), type text), #"Merged Queries" = Table.NestedJoin(#"Added Custom", {"week"}, #"Added Custom1", {"week"}, "Added Custom1", JoinKind.LeftOuter), #"Expanded Added Custom1" = Table.ExpandTableColumn(#"Merged Queries", "Added Custom1", {"Week Day"}, {"Week Day"}), #"Removed Other Columns" = Table.SelectColumns(#"Expanded Added Custom1",{"Date", "Week Day"}) in #"Removed Other Columns"
Hi @rezaaditia ,
I hope you have a calendar ( date ) table in your model.
Do the following
1. Create a calculated column called Week
Hi @CheenuSing
its working, great formula and simple 🙂
another question, how to make the weekname sort like calendar yah, its should be started
W-06/10, W-06/17, W-07/01 and .....
Thanks
Hi @rezaaditia ,
Try the following
1. Add another column
YearWeek = Year(Table[Date]) * 100 + Table[WeekNum]
( Replace Table by your tablename)
2. Using the modelling tab set the Sort order for WeekName to YearWeek.
Cheers
CheenuSing
Thanks a lot @CheenuSing , its working 🙂
but just to make sure if what i am doing is right
so i added new column "YearWeek" and then put it in axis under weekname.
This is correct or you have another method ?
Thanks
Hi @rezaaditia ,
You should not add the YearWeek in the axis name.
what you should do is select the Calculated column WeekName in the Fields pane.
Go to the modelling pane
From the sort by column set it to YearWeek.
Cheers
CheenuSing
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |