Need help writing DAX fnction that picks up values entered within a full calendar week "N Weeks Ago"

[E12345]

Hi!

I need to write a dax function that searches for entries made accross a specified number of calendar weeks ago.

For example, how do I pick up data entered on the week that happened 64 weeks ago (or on a week 77 weeks ago, etc). \

For example, if today is 10/24/2022, the "week 64 weeks ago" would pick up any entry that occurred from "08/01/2021 to 08/07/2021" (or within the full calendar week 64 weeks ago). 

I am new to dax, so any input would be appreciated. Ideally the "64" shoyuld be a variable, because I want to vary it and create multiple dashboards with different values for "Data for the full calendare week N Weeks Ago". Thank You!

[AilleryO]

Even better and easier than my cumulative column 🙂

[tamerj1]

Hi @E12345 

you can place the following measure in the filter pane, select "is not blank" then apply the filter

FilterMeasure =
VAR WeekShift = 64 -- can be a parameter 
VAR CurrentWeek =
    MAX ( 'Date'[Week Number] )
RETURN
    COUNTROWS (
        CALCULATETABLE (
            'Table',
            REMOVEFILTERS ( 'Date' ),
            'Date'[Week Number] = CurrentWeek - WeekShift
        )
    )

[AilleryO]

Hi,

Just to complete the solution of @tamerj1 , this will work if you have a column with cumulative week numbers.

Otheriwse this might not work, if Week number is 45, minus 64 makes a negative number not a week num from the previous year.

But you create a column in your date table with cumulative week numbers, it should work.

Hope it helps

[tamerj1 · Accepted solution]

@AilleryO 

You are absolutely right!

ccan be amended as follows

FilterMeasure =
VAR WeekShift = 64 -- can be a parameter 
VAR CurrentDate =
    MAX ( 'Date'[Date] )
RETURN
    COUNTROWS (
        CALCULATETABLE (
            'Table',
            REMOVEFILTERS ( 'Date' ),
            'Date'[Date] >= CurrentDate - WeekShift * 7,
            'Date'[Date] < CurrentDate - WeekShift * 6
        )
    )

[E12345]

Great idea, thank you! My problem is - we do not have a calendar table in the system and we are not allowed to create our own tables... So, will wait until I can use this code (until we have a calendar table in place).