Dynamic filter between range dates

[Daveed1973]

Okay, here is a head scratcher for you all. I have a table in Power BI which list contract dates. So it will have columns such as below..

Product     StartDate    EndDate     LOS

Product1    20/11/17      30/11/17    11

Product2    02/01/16      19/03/16    78

I need to work out how long a product will have been in the contract for and have done that quite easily using DATEDIFF in the SQL query to give me a LengthOfStay column (LOS) as you can see above.

My issue is that I need to apply a date slicer in Power BI which filters the start end and end date. Again, not an issue but I need the LOS column to change to reflect this. So if I filter the start date and end date to only show Jan 2016, I need the LOS column to reflect only the number days in that month. Product2 above would show a LOS of 30 days.

Because this filter is applied in Power Bi, how do I get it to calculate the LOS column dynamically depending on what the date slicer is set to?

[v-jiascu-msft · Accepted solution]

Hi @Daveed1973,

First, we can't put "StartDate" or "EndDate" in the slicer, which would hide the record directly. So we need a new table "Calendar". No relationship is needed.

Calendar =
CALENDAR ( DATE ( 2016, 1, 1 ), DATE ( 2017, 12, 31 ) )

Second, we can use this formula. 

LengthOfStay =
VAR MinDay =
    IF ( ISFILTERED ( 'Calendar'[Date] ), FIRSTDATE ( 'Calendar'[Date] ) )
VAR MaxDay =
    IF ( ISFILTERED ( 'Calendar'[Date] ), LASTDATE ( 'Calendar'[Date] ) )
RETURN
    IF (
        ISBLANK ( MinDay ),
        9999,
        IF (
            MIN ( 'Table1'[StartDate] ) > MaxDay,
            0,
            IF (
                MIN ( 'Table1'[EndDate] ) < MinDay,
                0,
                IF (
                    MIN ( 'Table1'[StartDate] ) >= MinDay
                        && MIN ( 'Table1'[EndDate] ) <= MaxDay,
                    DATEDIFF ( MIN ( 'Table1'[StartDate] ), MIN ( 'Table1'[EndDate] ), DAY ) + 1,
                    IF (
                        MIN ( 'Table1'[StartDate] ) >= MinDay
                            && MIN ( 'Table1'[EndDate] ) >= MaxDay,
                        DATEDIFF ( MIN ( 'Table1'[StartDate] ), MaxDay, DAY ) + 1,
                        IF (
                            MIN ( 'Table1'[StartDate] ) <= MinDay
                                && MIN ( 'Table1'[EndDate] ) <= MaxDay,
                            DATEDIFF ( MinDay, MIN ( 'Table1'[EndDate] ), DAY ) + 1,
                            IF (
                                MIN ( 'Table1'[StartDate] ) <= MinDay
                                    && MIN ( 'Table1'[EndDate] ) >= MaxDay,
                                DATEDIFF ( MinDay, MaxDay, DAY ) + 1
                            )
                        )
                    )
                )
            )
        )
    )

Best Regards!

Dale

[Daveed1973]

That's awesome, thanks Dale. I've made a few tweaks to this to suit my needs a little more.

LengthOfStay = 
VAR MinDay =
    IF ( ISFILTERED ( 'Calendar'[Date] ), FIRSTDATE ( 'Calendar'[Date] ) )
VAR MaxDay =
    IF ( ISFILTERED ( 'Calendar'[Date] ), LASTDATE ( 'Calendar'[Date] ) )
RETURN
 -- No filter selected, set LengthOfStay to difference between StartDate and TempEndDate
    IF (ISBLANK ( MinDay ), DATEDIFF (MIN(Table1[StartDate]), MAX(Table1[TempEndDate]),DAY) + 1,
  -- If StartDate is after filtered End Date then set length of stay to 0
  IF (MIN ( 'Table1'[StartDate] ) > MaxDay, 0,
   -- If EndDate before the filtered start date then set LOS to 0
   IF ( MIN ( 'Table1'[TempEndDate] ) < MinDay, 0, 
    
    -- If the StartDate is after the filtered start date and EndDate is less than the filtered end date -- Workout difference between StartDate and EndDate
    IF (MIN ( 'Table1'[StartDate] ) >= MinDay && MIN ( 'Table1'[TempEndDate] ) <= MaxDay, DATEDIFF ( MIN ( 'Table1'[StartDate] ), MIN ( 'Table1'[TempEndDate] ), DAY ) + 1,
                
     -- If StartDate is after filtered start date and EndDate is after filtered end date then workout difference between StartDate and filtered end date
     IF (MIN ( 'Table1'[StartDate] ) >= MinDay && MIN ( 'Table1'[TempEndDate] ) >= MaxDay, DATEDIFF ( MIN ( 'Table1'[StartDate] ), MaxDay, DAY ) + 1,
                        
      -- StartDate is before filtered start date and EndDate before filtered end date workout difference bewteen filtered start date and EndDate
      IF (MIN ( 'Table1'[StartDate] ) <= MinDay && MIN ( 'Table1'[TempEndDate] ) <= MaxDay, DATEDIFF ( MinDay, MIN ( 'Table1'[TempEndDate] ), DAY ) + 1,
                            
       -- StartDate before filtered start date and EndDate after filtered end date workout difference between filtered start and end date
       IF (MIN ( 'Table1'[StartDate] ) <= MinDay && MIN ( 'Table1'[TempEndDate] ) >= MaxDay, DATEDIFF ( MinDay, MaxDay, DAY ) + 1
                            )
                        )
                    )
                )
            )
        )
    )

I've added a TempEndDate column to Table1 with the following calculation. This is to help me when the EndDate is blank and allows me to do a LengthOfStay calculation up to the current date.

TempEndDate = IF(ISBLANK(Table1[EndDate]),Today(),Table1[EndDate])

This seems to work. However I need to add a chart which shows the length of stay based on these figures and my chart is giving me some odd figures.

As you can see the totals for LengthOfStay are wrong as too are the totals in the bar chart. Based on the date filter I have applied Product3 total should be 195, Product2 total should be 68. I also do not get the option to change how the value LengthOfStay is displayed, for example, SUM, AVERAGE etc.

Not sure what is going on here?