Return date of next Wednesday in a tile

[Anonymous]

Hey,

I am looking for some DAX to use in a measure , which will return the date of the next Wednesday. which I want to display in a tile in the report

I have tried the below but it is not very graceful and additionally it doesn't work (it needs a column cannot use today()).

Does anyone have any ideas?

var DayNum = WEEKDAY(TODAY(),2)

Return if(DayNum = 1, DATEADD(TODAY(),2,DAY), IF(

DayNum = 2, DATEADD(Today(),1,DAY), IF(

DayNum = 3, Today(), IF(

DayNum = 4, DATEADD(Today(),6,DAY), IF(

DayNum = 5, DATEADD(Today(),5,DAY), IF(

DayNum = 6, DATEADD(Today(),4,DAY), DATEADD(Today(),3,DAY)))))))

[PaulDBrown · Accepted solution]

@Anonymous   

If you are calculating "next wednesday" based on today's date, try this:

Please change the “;” to “,” in the measure

Next wednesday = 
VAR weeknumtod = WEEKDAY(TODAY()) 
Return 
IF(weeknumtod<5; TODAY()+4-weeknumtod; IF(weeknumtod <8; TODAY() +8-weeknumtod + 3)) 

You may have to tweak it depending on the date you wish to see if today is actually "wednesday" (whether you want to see today's date, or next week's wednesday's date)

 At the moment it will return today’s date if it is wednesday. If you want it to be next wednesday, it being wednesday today, you will need to add am IF(weeknum = 4; TODAY() +7)

at the very beginning of the IF clause in the return statement.

You can play around to see the results by hard coding a date in the VAR "seldate" in this measure:

Next wednesday (hard coded "today") = 
VAR seldate = DATE(2019;11;16)
VAR weeknumtod = WEEKDAY(seldate)
Return
IF(weeknumtod<5; seldate +4-weeknumtod; IF(weeknumtod <8; seldate +8-weeknumtod + 3))

[MartynRamsden]

Hi @Anonymous 

Try this (where 'Table'[Column1] is your date column reference):

Next Wednesday =
VAR SelDate = SELECTEDVALUE ( 'Table'[Column1] )
VAR DayNo = WEEKDAY ( SelDate )
VAR NextWed =
SWITCH (
    TRUE(),
    DayNo = 1, SelDate+3,
    DayNo = 2, SelDate+2,
    DayNo = 3, SelDate+1,
    DayNo = 4, SelDate+7,
    DayNo = 5, SelDate+6,
    DayNo = 6, SelDate+5,
    DayNo = 7, SelDate+4
)
RETURN NextWed