cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Cfrancis
New Member

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
Community Support
Community Support

Re: Standalone date slicer for a formula

hi, @Cfrancis 

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.
Highlighted
Cfrancis
New Member

Re: Standalone date slicer for a formula

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

Cfrancis
New Member

Re: Standalone date slicer for a formula

Capture.PNG

Community Support
Community Support

Re: Standalone date slicer for a formula

hi, @Cfrancis 

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
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors