Forum Discussion

HenryJS's avatar
HenryJS
Post Prodigy
6 years ago
Solved

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/...
Data Protection
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    6 years ago

    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

HenryJS's avatar
HenryJS
Post Prodigy
6 years ago

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's avatar
Greg_Deckler
Community Champion
6 years ago

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's avatar
    HenryJS
    Post Prodigy
    6 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        HenryJS
        Post Prodigy
        6 years ago

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