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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Standalone date slicer for a formula

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)

4 REPLIES 4
v-lili6-msft
Community Support
Community Support

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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
Not applicable

Capture.PNG

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.