cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Cfrancis Frequent Visitor
Frequent Visitor

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

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.
Cfrancis Frequent Visitor
Frequent Visitor

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 Frequent Visitor
Frequent Visitor

Re: Standalone date slicer for a formula

Capture.PNG

Community Support Team
Community Support Team

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
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors