Compairing a date measure to a date column in the same dimension table

[arc_gts]

I'm trying to create a filter to determine the inclusion or exclusion of records in a dimension table based on whether the Threshold Date for each record is less than the calculated Last Date measure. There's probably a much more intelligent way to do this, but I'm kind of stumbling my way around here and am not very good with DAX.

The threshold date column is native to the imported Excel spreadsheet that I'm using as a dimension table; the measure is based on a calendar dimension table imported from a data warehouse, and I'm selecting date ranges using a date slicer in the report. I've tried calculating the Last Date two different ways:

1) Using CALCULATE and LASTDATE: CALCULATE(LASTDATE('Calendar'[Date (EDW)]), ALLSELECTED('Calendar'[Date (EDW)]))

2) Using MAXX: MAXX(ALLSELECTED('Calendar'),'Calendar'[Date (EDW)])

Both work fine as measures. If I try to convert them into dimensions (or create them as columns instead of measures), they'll ignore the slicer and just return the last date from my entire calendar table. Similarly, if I try to create an IF statement returning a one or zero based on whether the Threshold Date falls before the Last Date, the formula returns a value of one for all records in the dimension table with a Threshold Date that falls before the last date in my calendar table.

Is there a way to compare my Last Date and Threshold Date that will still apply the slicer to the Last Date?

I can't create an active relationship between the calendar dimension table and the dimension table containing Threshold Dates because the calendar dimension table already joins to two fact tables which then join to the dimension table containing Threshold Dates.

[arc_gts]

PBIX file link

[smpa01 · Accepted solution]

@arc_gts  you can write this two measures

_isThresholdLesserThanLastSelected = if(MAX(Category[Threshold Date])<=[Category last date],0,1)

_isThresholdGTThanLastSelected = if(MAX(Category[Threshold Date])>[Category last date],0,1)

[arc_gts]

Goal: add an indicator that I can use as a filter to show or hide rows in a table based on a date in the table vs. a date in a slicer (no direct relationship between date slicer table and dimension table)

Expected outcome: rows in the dimension table with a greater date than the maximum selected slicer date value should have a value of zero, while rows in the dimension table with a value less than / equal to the maximum selected slicer date value should have a value of 1

Actual outcome: rows in the dimension table that have date value that exists in the calendar table have a value of 1, ignoring the selected range in the slicer; rows in the dimension table that have a date value that doesn't exist in the calendar table (greater than the max date value) have a value of zero

Please see below. It looks like I'm unable to upload a PBIX file.

[speedramps]

Try this Click here to download PBIX 

[speedramps]

Thanks.

You cant upload PBIX to the forum.

You need to save it on Onedrop or Dropbox with public view access and then share the link on the forum.

[smpa01]

@arc_gts provide sample data and expected output

[speedramps]

Hi

Sorry I dont understand you.

May I suggest rather than you try explain you rsolution (which does not work and you want us to fix).

That you simply explain the problem more clearly and with an exmaple, then I am sure we can all help you with this date filter issue.

Thanks