Power BI dynamic filter date between

[Enila123]

Hey everyone,

I've got this table:

area_ID        start_date         end_date       size

    1              01/01/16          10/01/16       500

    1              11/01/16          30/05/16       510

    1              01/06/16          NULL             520   (NULL means until today)

    2              05/01/16          20/01/16       380

and so on

On my Dashboard I need a slicer for the area_ID and a slicer for date. If I select area_ID = 1 and date = 03/01/16, I only want to see the first row. My problem is that I don't know how to filter the date correctly because the table only shows the start and end date. I created a new Table Calendar with all dates but I don't know how to combine that dynamically.

If anyone could help me how to solve that problem, I would be very very thankful! 🙂

[TomMartens · Accepted solution]

Hey,

here you will find a little pbix file.

The solution is based upon the following

• The separate calendar table is not related to the fact table containing "Date Start" and "Date End"

I created this measure in the fact table

Check Date Selection = 
var DateSelected = MINX(VALUES('Calendar'[Date]),'Calendar'[Date])
return
CALCULATE(
	SUMX(
		'Table1'
		,
        var DateStart = 'Table1'[DateStart]
        var DateEnd = IF(ISBLANK('Table1'[DateEnd]),TODAY(), 'Table1'[DateEnd])
        return
        IF(AND(DateStart <= DateSelected,
            DateEnd >= DateSelected)
			,1
			,BLANK()
		)
	)		
)

Basically this measure iterates through all the rows of the facttable and checks if the selected date from the slicer is between DateStart and DateEnd, if so the row is marked with the value 1 (the number is meaningless further on, it is just necessary, that it is not BLANK().

Be aware that this measure is used in Visual Level filters of the the table visual, here i use "Check Date Selection" is not blank.

Hopefully this is what you are looking for

Regards

Tom

[Enila123]

That's great, I'm very happy!

Thank you very very much, you helped me a lot 🙂