Table with DateTable and measure that returns 1 for the last 7 days selected only

[Wonter]

Hi all,

I'm new to DAX and I can't workout a question I have, but it sounds so simple. I'm looking for a measure that returns a 1 for the last 7 days of the days I selected with my slicer (settings between). I have a DateTable with Dates for every day between 01/01/2019 and today (05/01/2024). If I select for example as an End Date for my Slicer 10/12/2023, I would like to have the following output using a Table with DateTable[Date]  as an field and the measure as a field:

Date                     Measure
10/12/2023           1

09/12/2023           1

08/12/2023           1

07/12/2023           1

06/12/2023           1

05/12/2023           1

04/12/2023           1

03/12/2023           0

02/12/2023           0

01/12/2023           0

30/11/2023           0

29/11/2023           0

28/11/2023           0

etc.

I hope someone can help me, as the things that I try returns jumping numbers or close to the todays date unfortunately. Thank you in advance.

[Dangar332]

hi, @Wonter 

you need to make another datetable  which is not connected with your original datetable

use new datetable date as a slicer and old datetable date as table visual and add below column in that visual

Measure 2 = 
var a = SELECTEDVALUE('new datetable'[Date])
return
IF(MIN('old datetable'[Date])<=a && MIN('old datetable'[Date])>a-7,1,0)

[Wonter]

Thank you for your quick reply @Dangar332 . is this the only way? Of doing it with an extra DateTable? As I am using my Date Table also for different tables, which is crucial for selecting the correct periods. So unfortunately I need my current DateTable in the slicer and can't make it independent. It would be great if there is another option where I could use the same DateTable I have without an independent datetable.

[amitchandak]

@Wonter , if you have selected a range greater than 7 days

You can use a measure

Meausre =

var _max = maxx(allselected(Date), Date(Date[date])

return

if(Max(date[Date]) >= _max -6 && Max(date[Date]) <= _max ,1,0)

But if you plan to select a range less than 7 days. Then slicer needs to be on independent date table

//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
return
if(Max(date[Date]) >= _max -6 && Max(date[Date]) <= _max ,1,0)

Need of an Independent/disconnected Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI

[Wonter]

Thank you for your quick reply @amitchandak. Unfortunately the first one is not working. Is there another way of doing this without creating a new independent Date Table? As I need my Date Table also for different tables.