Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

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
Super User

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@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". 

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.

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

 

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

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.

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.