Display Friday's date in all the rows of a column.

[Anonymous]

Hi,

Below is the table I'm working on, to which I'm to add another column that will display the Friday's date of the week in all its rows.

Date                Day   

12/5/2019      Sun   

13/5/2019      Mon        

14/5/2019      Tue

15/5/2019      Thurs

16/5/2019      Fri

17/5/2019      sat

I expect as the following output:

Date                Day         Expected Output

12/5/2019      Sun         17/5/2019      

13/5/2019      Mon        17/5/2019         

14/5/2019      Tue          17/5/2019      

15/5/2019      Wed        17/5/2019      

16/5/2019      Thur        17/5/2019      

17/5/2019      Fri           17/5/2019   

18/5/2019      Sat          24/5/2019

19/5/2019      Sun         24/5/2019

20/5/2019      Mon        24/5/2019

21/5/2019      Tues        24/5/2019

22/5/2019      Wed        24/5/2019

23/5/2019      Thurs      24/5/2019

24/5/2019      Fri           24/5/2019

23/5/2019      Sat          24/5/2019

Using dax how can I achieve this one.

[v-xicai]

Hi  @Anonymous ,

Does that make sense? If so, kindly mark my answer as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

Best regards

Amy

[v-xicai · Accepted solution]

Hi @Anonymous ,

You can create columns using DAX below.

Day = SWITCH(WEEKDAY(Table1[Date],2),1,"Mon",2,"Tue",3,"Wed",4,"Thur",5,"Fri",6,"Sat",7,"Sun")

WEEKNUM = WEEKNUM(Table1[Date],2)

Rank = RANKX(Table1,Table1[Date],,ASC)

Expected Output = var d6=CALCULATE(MAX(Table1[Date]),FILTER(Table1,Table1[Rank]=EARLIER(Table1[Rank])+6))

                 var d5=CALCULATE(MAX(Table1[Date]),FILTER(Table1,Table1[Rank]=EARLIER(Table1[Rank])+5))

                 return IF(WEEKDAY(Table1[Date],2)=6, d6,IF(WEEKDAY(Table1[Date],2)=7,d5,CALCULATE(SUM(Table1[Date]),FILTER(Table1,Table1[WEEKNUM]=EARLIER(Table1[WEEKNUM])&&WEEKDAY(Table1[Date],2)=5))))

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[marksn]

Hi Amy,

Any way to recreate your solution by a DAX measure instead of a DAX column?
I have a similar need but rather instead of dates from a table, the base date would be TODAY().
Thanks.

Nevermind, I seem to have figured it out:

WeekEndDate = TODAY() - WEEKDAY(TODAY(),2) + 5