Standalone date slicer for a formula

[Anonymous]

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)

[v-lili6-msft]

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

[Anonymous]

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

[Anonymous]

[v-lili6-msft]

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