Data Table: Weekend Ending Column

[HenryJS]

Hi all,

I have the below data table. I require 2 columns to be added:

• Week Ending
  • Weeks denoted by ending on the Sunday for previous week
  • i.e. Week Ending 12/04 is for the week 06/04/20 - 12/04/20
  • Is it possible to have the Field Value of this column to be "Week No. 1 - WE 12/04"
• Quarter
  • First week of Quarter is the Week Ending 12/04/20

Date Table:

Calendar = ADDCOLUMNS(CALENDAR(DATE(YEAR(MINX('Export Placements','Export Placements'[PlacementStartDate])),1,1),DATE(YEAR(MAXX('Export Placements','Export Placements'[PlacementDate])),12,31)

),"YEar",YEAR([Date]),"WeekNum",YEAR([Date])&"-"& FORMAT(WEEKNUM([Date]),"00"))

[Greg_Deckler]

Week Ending:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Week-Ending/m-p/389293#M120

[HenryJS]

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

[Greg_Deckler]

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

[HenryJS]

Hi @Greg_Deckler 

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?

[Greg_Deckler]

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

[HenryJS]

@Greg_Deckler thank. The date format is still showing mm/dd/yyyy? And they aren't in order in the columns

[Anonymous]

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

[HenryJS]

@Anonymous 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?

WeekEnding Trial =

VAR myWeekNum = WEEKNUM([Date])

VAR myYear = YEAR([Date])

VAR myEndDate = CALCULATE(MAX([Date]),FILTER(ALL('Calendar'),YEAR([Date])=myYear && 'Calendar'[WeekNum]=myWeekNum && 'Calendar'[WeekDay]=7))

VAR myEndDate1 = IF(NOT(ISBLANK(myEndDate)),myEndDate,CALCULATE(MAX([Date]),FILTER(ALL('Calendar'),YEAR([Date])=myYear+1 && 'Calendar'[WeekNum]=1&&'Calendar'[WeekDay]=7)))

VAR myEndDate2 = IF(NOT(ISBLANK(myEndDate1)),myEndDate1,MAX([Date]))

RETURN "W" & myWeekNum & " Week Ending " & FORMAT ( myEndDate2, "dd/mm/yyyy" )

[Anonymous · Accepted 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

[amitchandak]

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