Current week based on custom field

[EmaVasileva]

Hi all,

I need some help with a calculated column. I have the following data:

Date	Week	Custom week	Value
June 17	25	24	5
June 18	25	24	6
June 19	25	24	5
June 20	26	24	8
June 21	26	24	9
June 22	26	24	12
June 23	26	24	2
June 24	26	25	3
June 25	26	25	5
June 26	26	25	5
June 27	27	25	6
June 28	27	25	7
June 29	27	25	3
June 30	27	25	4
July 1	27	26	5
July 2	27	26	3
July 3	27	26	3
July 4	28	26	8
July 5	28	26	9
July 6	28	26	9
July 7	28	26	1
July 8	28	27	2

All data is for 2021 and new dates will be added in future. The week field - the week number starts from Sunday.

My custom week starts from Thursday. I need a Yes/No calculated column which I can use as a Slicer to return Yes/No for "Is current week"? (based on my custom week)

So the desired result should be; If I select from "Is current week"- 'Yes', a bar chart should return all values by dates for the current custom week (in this example 25 week- June 24 until June 30). If today is July 1st and the Current week is Yes - there should be values only for July 1st, and etc.

Thank you.

[mahoneypat · Accepted solution]

You can add a DAX column using a pattern like this

CurrentWeek =
VAR todaydate =
    TODAY ()
VAR thisdate = 'Date'[Date]
VAR todayThursday =
    IF (
        WEEKDAY ( todaydate ) >= 5,
        todaydate - WEEKDAY ( todaydate ) + 5,
        todaydate - WEEKDAY ( todaydate ) - 2
    )
VAR thisThursday =
    IF (
        WEEKDAY ( thisdate ) >= 5,
        thisdate - WEEKDAY ( thisdate ) + 5,
        thisdate - WEEKDAY ( thisdate ) - 2
    )
RETURN
    IF ( todayThursday = thisThursday, "Y", "N" )

FYI that this would be easier in the query with M.  Also, please take a look at this article with some Date tables you could adapt, and then set your filter/slicer to Weeks from today as 0 (instead of "Y").

No Sort Date Tables! – Hoosier BI

Pat