topic Re: Need help writing DAX fnction that picks up values entered within a full calendar week "N Weeks Ago" in DAX Commands and Tips

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

E12345 — Mon, 24 Oct 2022 15:08:25 GMT

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!

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

tamerj1 — Mon, 24 Oct 2022 16:07:51 GMT

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 ) )

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

AilleryO — Mon, 24 Oct 2022 17:24:08 GMT

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

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

tamerj1 — Mon, 24 Oct 2022 17:35:25 GMT

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 ) )

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

AilleryO — Mon, 24 Oct 2022 17:50:59 GMT

Even better and easier than my cumulative column 🙂

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

E12345 — Thu, 09 Feb 2023 17:25:31 GMT

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).