Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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.
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)
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.
@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.
User | Count |
---|---|
123 | |
69 | |
67 | |
58 | |
52 |
User | Count |
---|---|
183 | |
90 | |
67 | |
62 | |
53 |