cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
HenryJS
Post Prodigy
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
yingyinr
Community Support
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.

View solution in original post

10 REPLIES 10
Greg_Deckler
Super User IV
Super User IV

Week Ending:

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

 

 


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

@ me in replies or I'll lose your thread!!!

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

 


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

@ me in replies or I'll lose your thread!!!

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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?

 

Capture1.JPG

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

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

@ me in replies or I'll lose your thread!!!

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

yingyinr
Community Support
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". 

sort by column.jpg

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.

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

 

Capture.JPG

 

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" )
 
 
 
yingyinr
Community Support
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.

View solution in original post

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors