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 guys,
Anyone have formulation to generate weekending data like the picture below (rounded in red line)?
I need colum of data that contain week ending date with format just like in the pict attached.
Thank you.
Solved! Go to Solution.
Hi @Irussaa ,
If you want a DAX formula, you could try the following DAX:
Column =
"W" & WEEKNUM ( 'DateDim'[WeekEnding], 1 ) & " "
& MONTH ( 'DateDim'[WeekEnding] ) & "/"
& DAY ( 'DateDim'[WeekEnding] )
Hi @Irussaa ,
If you want a DAX formula, you could try the following DAX:
Column =
"W" & WEEKNUM ( 'DateDim'[WeekEnding], 1 ) & " "
& MONTH ( 'DateDim'[WeekEnding] ) & "/"
& DAY ( 'DateDim'[WeekEnding] )
It works! @v-eachen-msft
But I have one more question, why is that first week ending in the beginning of the month (which is the first week in january) already generate WE2 not WE1?
Thank you
Hi @Irussaa ,
Replace WEEKNUM ( 'DateDim'[WeekEnding], 1 ) with WEEKNUM ( 'DateDim'[WeekEnding], 2 ). After my tests, it will work.
@v-eachen-msft I'm sorry can I ask one more question?
Why the WE data isn't arranged in the chart?
Thank you!
@Irussaa Please change your WE column "Sort By Column" to the WeekEndingNumber that needs to be derived from WeekEndingDate. (As you can't directly use the WeekEndingDate for Sort By Column, as WE is using that field)
Proud to be a PBI Community Champion
Hi @Irussaa
ColumnName =
Text.Combine({[Week]," ",Number.ToText([MonthN]),"/", Number.ToText(Date.Day
(Date.EndOfWeek([date]))})
Tell me if this works, since I did not have your table, I did this free hand. This is in Power Query.
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel
Proud to be a Super User!
Hi @Nathaniel_C
I try to apply
ColumnName = Text.Combine({[Week]," ",Number.ToText([MonthN]),"/", Number.ToText(Date.Day (Date.EndOfWeek([date]))})
Inside my data set:
[Week]
[MonthN]
Date.Day
(Date.EndOfWeek([date])
Hello @Irussaa ,
Here is a link to Power Query Power Query . It is the best place to add Calculated Columns.
However this is a way that you can do it in Power BI.
WE= table[Week] && " " && table[MonthN] && "/" && Day(table[Date]+ 7-WEEKDAY(table[Date],1))
In your post you show a table with columns. I took the name of the columns from that picture. You will want to replace "table" in my formula with your table name.
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel
Proud to be a Super User!
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 |
---|---|
110 | |
97 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |