Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Can I please get support from a PBI/DAX Expert, I’m trying to create a standalone date slicer, which when a date is selected the date is used in a Datediff formula.
The slicer is just for formula purposes.
Example;
DATEDIFF(‘TABLE1’[Doc Date], TABLE2[Selected Slicer Date], DAY)
hi, @Anonymous
First, you must know that calculated column and calculate table can't be affected by any slicer.
Notice:
1. Calculation column/table not support dynamic changed based on filter or slicer.
2. measure can be affected by filter/slicer, so you can use it to get dynamic summary result.
here is reference:
https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/
and from your formula, I think you want the column value is affected by the date slicer. This is not available.
Second, you could create a measure like below:
Step1:
Use CALENDAR Function to create a Standalone date table for slicer.
Then use this formula
DATEDIFF(Max(‘TABLE1’[Doc Date]), Max(TABLE2[Selected Slicer Date]), DAY)
If you still have the problem, please share some sample data and expected output.
Best Regards,
Lin
I didn’t give enough background information, sorry.
I’ve already created a “Date table”
Dates = CALENDAR(TODAY()-800,TODAY()+800)
I need an option where a non “Technical” user, has the flexibility of insert a changeable date which will affect a dates range.
If the user can use either a slicer/ or parameter, I’m not precious as to which one.
Product Name | 0-30 days | 31-60 days | 61-90 days | 91-120 days | 120+ days |
Product 1 | £19,500 | £2,00 | £9,700 | £8,200 | £22,500 |
Product 2 | £6,000 |
| £8,500 | £6,000 | £9,400 |
Date Range =
if([Days between] < 0, "0. Not Due",
if( and([Days between] >= 0, [Days between] <= 30), "1. 0 - 30 days",
if( and([Days between] >= 31, [Days between] <= 60), "2. 31 - 60 days",
if( and([Days between] >= 61, [Days between] <= 90), "3. 61 - 90 days",
if( and([Days between] >= 91, [Days between] <= 120), "4. 91 - 120 days",
if([Days between] >= 121, "5. 120+ days"))))))
I’ve created 2 measures to test selected slicer results;
Date selected = SELECTEDVALUE(Dates[Date])
IsFiltered = ISFILTERED(Dates[Date])
Both are working.
It’s not working when I put the ‘Date selected’ measure into a datediff formula.
I hope I have provided enough detail to help with a solution.
Thank you
hi, @Anonymous
Just use this formula
Measure2=DATEDIFF(MAX('cURRENT dEBTOR dETAILS'[Doc Date]),[SelectedDate],DAY)
If you still have the problem, please share a simple sample pbix file and expected out.
Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Best Regards,
Lin
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
123 | |
78 | |
48 | |
38 | |
37 |
User | Count |
---|---|
196 | |
80 | |
70 | |
51 | |
42 |