Create a slicer or filter from a Measure

[Anonymous]

I am trying to create a slicer or filter for a measure

My source data only has a "Billing Start date" and no "Billing Finish Date" we only have the term of contract i.e. 12, 24, 36, etc

I have created a measure to create "Billing Finish Date"

Billing Finish Date = VAR vStartDate = MAX ( 'All Bookings'[Billing Start] ) VAR vTermMonths = MAX ( 'All Bookings'[Term] ) VAR vFinishDate = IF ( vTermMonths <> 1, EDATE ( vStartDate, vTermMonths - 1 ) ) VAR vResult = IF ( HASONEVALUE ( 'All Bookings'[Contract#] ), vFinishDate ) RETURN vResult

From this, I have created an "In Range" measure, which equals either "Expired" or "Booked"

In Range = IF('All Bookings'[Billing Finish Date]<=TODAY(),"Expired","Booked")

Now I need to Filter or add slicer against "Expired", "Booked"

Any help, advice would be appreciated 

[v-xiaotang · Accepted solution]

Hi @Anonymous 

Based on your explanation, I create a sample file, you can take it for reference. See sample file attached below.

Besides, the easiest way is to create a column,

In Range Col = IF('All Bookings'[Billing Finish Date]<=TODAY(),"Expired","Booked")

Then, drag this column into the slicer:

Result:

Best Regards,

Community Support Team _ Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Works like a dream thank you 🙂

[amitchandak]

@Anonymous , You have to create an independent table(NewTable) with these two values

Create measures using this

countx(filter(values(Bookings[Contract#]), [In Range] =max(NewTable[Value])),Bookings[Contract#])

Check my video on a similar topic

Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k

[Anonymous]

As I can't get this to work do I have any other options in being able to filter my data?

[Anonymous]

When I try to create the new measure suggested with the new table name I get lots of errors

Any help will be appreciated

[Anonymous]

Thank you for such a quick answer

I have watched the video but as yet can’t get my head around what needs to be done, I believe I only need a small portion of your video and need two columns

Contract # and In Range

Is that correct?