cancel
Showing results for
Did you mean:
Post Prodigy

## Data Table: Weekend Ending Column

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"))
1 ACCEPTED SOLUTION
Community Support

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
10 REPLIES 10
Super User IV

Week Ending:

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

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Post Prodigy

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

Super User IV

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

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Post Prodigy

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?

Super User IV

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

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Post Prodigy

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

Community Support

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Post Prodigy

@yingyinr 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" )

Community Support

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User IV

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

Proud to be a Super User!

Announcements