Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am trying to figure out how to filter a date field for 2 weeks prior the current date. The dashboard I am creating has to be completely automated, so using filter where I am selecting a spefic date will not work. This particular dashboard is keeping track of IT Tickets, the field I want to filter for is dateEntered (which is the date the ticket is entered into the system) and I want to show information for tickets opened prior to 2 weeks ago.
The formula I think is closest to my goal is:
Solved! Go to Solution.
Hi @Anonymous ,
First you need two separate tables, a date table and a fact table. They are not related to each other.
Then create a measure, like the following.
Measure =
VAR _selected =
MAX ( Slicer[Date] )
VAR _start = _selected - 14
RETURN
IF (
SELECTEDVALUE ( 'Table'[Date] ) >= _start
&& SELECTEDVALUE ( 'Table'[Date] ) <= _selected,
1
)
Then put the measure in filter pane and set it show items which is 1.
The output:
Pbix in the end you can refer.
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
14_days_ago =
VAR __LAST_DATE = LASTDATE(DateTable[Date])
VAR __FIRST_DATE = FIRSTDATE(DateTable[Date])
return
AVERAGEX(
DATESBETWEEN(
DateTable[Date],
DATEADD(__FIRST_DATE, -14, DAY),
DATEADD(__LAST_DATE, -14, DAY)
),
CALCULATE([WHATEVER YOU WANT TO CALCULATE])
)
What does the CALCULATE at the end of the DAX expression do exactly? Can you giva an example of something to "plug in" there please?
Thank you
If you have a measure: lines = countrows(table)
in the function, that would be "calculate[lines]" <-- measure name
One more thing, the function is for daily average, if you want daily sum, change AVERAGEX to SUMX.
Hi @Anonymous ,
First you need two separate tables, a date table and a fact table. They are not related to each other.
Then create a measure, like the following.
Measure =
VAR _selected =
MAX ( Slicer[Date] )
VAR _start = _selected - 14
RETURN
IF (
SELECTEDVALUE ( 'Table'[Date] ) >= _start
&& SELECTEDVALUE ( 'Table'[Date] ) <= _selected,
1
)
Then put the measure in filter pane and set it show items which is 1.
The output:
Pbix in the end you can refer.
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you so much for a quick response!
I have been working through this. Is the slicer what effects the date? I have completed all the steps you have shown and the date is not effected by the DAX formula.
Hi @Anonymous ,
Works fine with my pbix.
1 Ensure that the two tables have no relationship
2 measure is placed in the filter pane of the date table visual
Best Regards
Community Support Team _ chenwu zhu
Hi @Anonymous ,
How about using the relative date function from the filter pane? 🙂
Let me know if this helps!
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! |
#proudtobeasuperuser |
How would you use this to only show the previous 2 weeks from the current date? This is similar to something I need to create a table that will show information for the three years prior. Right now it would show sales total for each month in 2019, but I would the table to update to only show 2020 sales when we get into 2023.
Thank you for the suggestion tomfox, but I need to filter for data that was created prior to 2 weeks ago.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
19 |