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 all,
I have the below data table. I require 2 columns to be added:
Date Table:
Solved! Go to Solution.
Hi @HenryJS ,
Can I know which formula used in calculated column "Calendar'[WeekDay]"? It should be just like below one:
Calendar'[WeekDay]=WEEKDAY(Calendar[Date],2) |
Best Regards
Rena
Week Ending:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Week-Ending/m-p/389293#M120
Hi @Greg_Deckler I need this for Columns in a Matrix.
It won't let me drag the mWeekEnding measure to columns for the matrix
The column version is in the PBIX file attached to that post.
WeekEnding =
VAR myWeekNum = WEEKNUM([Date])
VAR myYear = YEAR([Date])
VAR myEndDate = CALCULATE(MAX([Date]),FILTER(ALL(Calender),YEAR([Date])=myYear && Calender[WeekNum]=myWeekNum && Calender[WeekDay]=7))
VAR myEndDate1 = IF(NOT(ISBLANK(myEndDate)),myEndDate,CALCULATE(MAX([Date]),FILTER(ALL(Calender),YEAR([Date])=myYear+1 && Calender[WeekNum]=1&&Calender[WeekDay]=7)))
VAR myEndDate2 = IF(NOT(ISBLANK(myEndDate1)),myEndDate1,MAX([Date]))
RETURN "W" & myWeekNum & " Week Ending " & myEndDate2
Ok - I got the column version to work. When put into matrix columns the column headers don't follow the correct date order - please see below.
Is it also possible to change the date format to English dd/mm/yyyy?
Sure it's the last line:
WeekEnding =
VAR myWeekNum = WEEKNUM([Date])
VAR myYear = YEAR([Date])
VAR myEndDate = CALCULATE(MAX([Date]),FILTER(ALL(Calender),YEAR([Date])=myYear && Calender[WeekNum]=myWeekNum && Calender[WeekDay]=7))
VAR myEndDate1 = IF(NOT(ISBLANK(myEndDate)),myEndDate,CALCULATE(MAX([Date]),FILTER(ALL(Calender),YEAR([Date])=myYear+1 && Calender[WeekNum]=1&&Calender[WeekDay]=7)))
VAR myEndDate2 = IF(NOT(ISBLANK(myEndDate1)),myEndDate1,MAX([Date]))
RETURN myEndDate2
@Greg_Deckler thank. The date format is still showing mm/dd/yyyy? And they aren't in order in the columns
Hi @HenryJS ,
You can make a little adjustment on the formula of calculated column which provided by @Greg_Deckler for the date format change:
WeekEnding = VAR myWeekNum = WEEKNUM([Date]) VAR myYear = YEAR([Date]) VAR myEndDate = CALCULATE(MAX([Date]),FILTER(ALL(Calender),YEAR([Date])=myYear && Calender[WeekNum]=myWeekNum && Calender[WeekDay]=7)) VAR myEndDate1 = IF(NOT(ISBLANK(myEndDate)),myEndDate,CALCULATE(MAX([Date]),FILTER(ALL(Calender),YEAR([Date])=myYear+1 && Calender[WeekNum]=1&&Calender[WeekDay]=7))) VAR myEndDate2 = IF(NOT(ISBLANK(myEndDate1)),myEndDate1,MAX([Date])) RETURN "W" & myWeekNum & " Week Ending " & FORMAT ( myEndDate2, "dd/mm/yyyy" ) |
And for the order of columns, you can use the "Sort By Column" functionality to accomplish this. Click on the Data tab, highlight the row, select "Column tools" tab, then "Sort By Column".
Best Regards
Rena
@v-yiruan-msft thank you - that worked.
However it is not displaying the correct 'Week Ending' date.
For example below, it should be stating "W15 Week Ending 12/04/2020"
How can I ammend to reflect this?
Hi @HenryJS ,
Can I know which formula used in calculated column "Calendar'[WeekDay]"? It should be just like below one:
Calendar'[WeekDay]=WEEKDAY(Calendar[Date],2) |
Best Regards
Rena
You can create a week ending measure like
"Week No. 1 - WE 12/04"
Ending = "Week No. 1 - WE " & Format([Week End date],"DD/MM")
For Week end date refer this file
https://www.dropbox.com/s/a9xq913pgvuzg2x/sales_analytics_weekWiseMon_sun.pbix?dl=0
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 |
---|---|
112 | |
100 | |
77 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |