Dax formula to get the wednesday's date based on week selection

[aktripathi2506]

Hi,

I have a date column for past 5 years, based on which I created year and weeknum slicer.

When I select the year and week number from the slicer I want to get the wednesday's date.

Please suggest this simple but tricky task.

Thanks.

[Greg_Deckler]

You could use the WEEKDAY function to get the WEEKDAY for each date in a custom column and then filter your visual to just the Wednesday's

https://msdn.microsoft.com/en-us/library/ee634550.aspx

[aktripathi2506]

HI @Greg_Deckler, I actually wanted to get wednesday's date so I can use it in another formula.

I can put only one slicer for week (can not put day slicer), so what I was expecting is when I select week (say week 35) then it should get the wednesday's date for that week and subtract this date from deadline date to fet the remaining week number.

[Greg_Deckler · Accepted solution]

@aktripathi2506, OK, not sure I 100% understand your scenario, but here is what I came up with:

Started with this Enter Data query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc7BCQAhEEPRXjwvONF11qlF7L8NF0FMIJd3yh8j9Vz+GTzNZytY1US4iiwojMp4GY3hjI/RGSGnJtIgiYBUQDJwOuYC", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}})
in
    #"Changed Type"

Created these three custom columns:

Year = YEAR([Date])

WeekNum = WEEKNUM([Date])

WeekDay = WEEKDAY([Date])

Created this measure:

Wednesday = IF(HASONEVALUE(Wednesday[WeekNum]),CALCULATE(MAX(Wednesday[Date]),FILTER(Wednesday,Wednesday[WeekDay]=4)),DATE(1900,1,1))

Put the Year and WeekNum slicers on a report page and the "Wednesday" measure as a card visualization and it seemed to work OK.