Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Wonter
Frequent Visitor

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

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.

4 REPLIES 4
Dangar332
Super User
Super User

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)

 

Dangar332_0-1704465238475.png

 

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
Super User
Super User

@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

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.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.