Date User Variable for multiple date columns

[KarenKazab]

Hi all, 

In sql/ ssrs this would be a doddle and sure its probably easy enough in PBI Desktop. 

In sql I would have a user entered variable like @Date which the user would enter a date then this would filter the query on 2 columns: 

WHERE DimProperty.PropertyHandoverDate <= @Date
AND (DimProperty.DisposedDate IS NULL OR DimProperty.DisposedDate > @Date)

So I need a variable slicer if thats a thing, where the user would enter the date they want and then the slicer would filter on two different columns using two different methods? Any help would be greatly appreciated 

Thanks

Karen

[Anonymous]

Hi Karen,

Please clarify

1)  How are you loading data into Power Bi -  Direct Query or Import Load

2)  Do you want to use the paramater at load time or after data is loaded you want to build mesaures on the filtered data using the slicer on date.

It will be good if you can post some data and the output expected on Google / One Drive and share the link here.

Best Regards

CheenuSing

[KarenKazab]

Sorry should have specified

1) Import load from a sql datawarehouse which is updated daily once in the morning

2) The parameter will be used after data load

Here is an example of data, I would only want the report to show 2 properties below as:

A is handed over after the parameter date to be used (30/06/2019)

B is handed over prior to the date but has been disposed of prior to the 30/06/2019

C is handed over prior to the date with no disposal date (blank)

D is handed over prior to the date and disposed of after the date so should still be included

Property Sequence Number	Property Handover Date	Disposed Date
A	01/07/2019
B	02/06/2019	23/06/2019
C	01/01/2000
D	01/01/2001	23/07/2019

Thanks

Karen

[KarenKazab]

I have managed to get my slicer value date selection into a measure so now I need to find a way to either use this measure as a filter on two existing column filters i.e. I have a filter which uses the column PropertyHandoverDate and I would like this to be filtered by the new measure, or I need to create another measure which is something like this:

My slicer value selection measure is called DateSelectionMeasure

DateFilter = IF('Property'[PropertyHandoverDate] <= 'Property'[DateSelectionMeasure], "Yes", "No")

But when I start typing the Property table columns dont appear only the measures I have created, how do I pull in the columns?