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 I need this for Columns in a Matrix. 

 

It won't let me drag the mWeekEnding measure to columns for the matrix

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

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