Single datefilter with start date from table 1 and end date from table 2

[Anonymous]

Hi Community,

In need of your help, been struggling on this one for hours!

I've got a data model with multiple fact tables and dimension tables:

I've got a different date filter (in yellow) for both data visuals (one from Sales and one from Actions), this works fine. 
At the moment I presented this report, users ask me to have only one date filter for both table-visuals.

Now comes the difficult part: when filtering per event, they ask to have the first date = DT_PlanningActions[Start_Action] and the last date = DT_Event[EventEndDate]

I've tried finding a measure that I can use to filter the dateslicer, but I can't find the solution.

Samble pbix here

Hope you folks can help me out!

[Anonymous]

@jdbuchanan71 
Now I get it! Thanks a bunch!!

[jdbuchanan71]

Sure, I can give it a go.  The first two variables _EventEnd and _Event just read the Date and Event_ID based on the event selected.  

Next we use the Event_ID to ficure out the PlanningStart date so now we have out two dates _PlanningStart and _EventEnd.

_Dates uses these two dates to generate the list of dates between the _PlanningStart and _EventEnd

Finally we count the rows in the dates table using the list of dates in the _Dates variable as a filter using TREATAS.  Since the count is filtered it will return BLANK when it is outside the range of dates.

[Anonymous]

Hi @jdbuchanan71 ,
That seems to do the trick!!
I'm also trying to understand how you did this;
The result of the measure is the amount of days between start & end, right? I don't understand how this filters the date filter to give the correct months 😅😲. Could you maybe help me understand?

[jdbuchanan71 · Accepted solution]

@Anonymous 

I think I know what you are going for and was able to get it.  I wrote a new measure for use in filtering the date slicer.

Date Filter = 
VAR _EventEnd =
    SELECTEDVALUE ( DT_Event[EventEndDate] )
VAR _Event =
    SELECTEDVALUE ( DT_Event[Event_id] )
VAR _PlanningStart =
    CALCULATE (
        SELECTEDVALUE ( DT_PlanningActions[Start_Action] ),
        TREATAS ( { _Event }, DT_PlanningActions[Event_id] )
    )
VAR _Dates =
    DATESBETWEEN ( DT_DateTable[Date], _PlanningStart, _EventEnd )
RETURN
    CALCULATE (
        COUNTROWS ( DT_DateTable ),
        KEEPFILTERS ( TREATAS ( _Dates, DT_DateTable[Date] ) )
    )

Then I applied that to the date slicer and set it to is not blank.

When you select an EventName the date slicer shows all the months between the two dates.