Need Help! Calculations based on Date Filters

[Anonymous]

Hello All,

Need help in handling couple of scenarios involving dates, i have a 2 date columns Start Date and Finish Date, and need to calculate number of days based on the below scenarios (Start Date is my slicer):

1. If Start Date and Finish Date fall within the slicer Date range, simple calculate the difference.

2. If Start Date (actual) for a record is less than minimum value selected in the slicer and Finish Date for the same record falls within the date range of the slicer, consider minimum of the slicer value selected as the start date (instead of the actual start date) and the actual finish date for calculating the difference.

3. If Start Date (actual) for a record falls within the date range selected and Finish Date is greater than the maximum of the slicer selection, consider actual start date and maximum of the slicer value selected as the finish date (instead of the actual finish date)

My idea is to have Considered Start Date and Considered Finish Date columns for each record, but this will have to change based on the filter selection.

Please help me figure out a way to solve this. 

Thanks in adance!

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

Calendar(a calculated table):

Calendar = CALENDARAUTO()

There is no relationship between two tables. You may create a measure as below.

Result = 
var _minslicerdate = CALCULATE(MIN('Calendar'[Date]),ALLSELECTED('Calendar'))
var _maxslicerdate = CALCULATE(MAX('Calendar'[Date]),ALLSELECTED('Calendar'))
var tab = 
ADDCOLUMNS(
    'Table',
    "Result",
    var _startdate = [Start Date]
    var _finishdate = [Finish Date]
    return
    SWITCH(
         TRUE(),
        _startdate>=_minslicerdate&&_finishdate<=_maxslicerdate,
        DATEDIFF(_startdate,_finishdate,DAY),
        _startdate<_minslicerdate&&_finishdate>=_minslicerdate&&_finishdate<=_maxslicerdate,
        DATEDIFF(_minslicerdate,_finishdate,DAY),
        _startdate<_minslicerdate&&_finishdate>_maxslicerdate,
        DATEDIFF(_minslicerdate,_maxslicerdate,DAY),
        _startdate>=_minslicerdate&&_startdate<=_finishdate&&_finishdate>_maxslicerdate,
        DATEDIFF(_startdate,_maxslicerdate,DAY)
    )
)
return
SUMX(
    tab,
    [Result]
)

Then you need to use the date column from 'Calendar' table to filter the result.

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you @v-alq-msft , this helps! 

[Anonymous]

Hi @FarhanAhmed ,

Below is a sample with the scenarios, hope this helps

Slicer - Start Date : 05/01/2020 to 05/31/2020
Scenario 1
Row ID	Start Date	Considered Start Date	Finish Date	Considered Finish Date	Expected Measure Value
1	5/15/2020	5/15/2020	5/20/2020	5/20/2020	5
Scenario 2					0
Row ID	Start Date	Considered Start Date	Finish Date	Considered Finish Date	Expected Measure Value
2	4/15/2020	5/1/2020	5/20/2020	5/20/2020	19
Scenario 3
Row ID	Start Date	Considered Start Date	Finish Date	Considered Finish Date	Expected Measure Value
3	5/15/2020	5/15/2020	6/20/2020	5/31/2020	16

[FarhanAhmed]

Date Difference = 
Var SlicerStartDate = MIN('Date'[Date])
Var SlicerEndDate = MAX('Date'[Date])
Var TableStartDt = MIN(DateLast[Date])
Var TableEndDt =MAX(DateLast[EndDate])
Return
CALCULATE(DATEDIFF(IF(SlicerStartDate<TableStartDt,TableStartDt,SlicerStartDate),IF(SlicerEndDate<TableEndDt,SlicerEndDate,TableEndDt),DAY))

Try to create something similar like this.

[FarhanAhmed]

Can you please share some sample data and required results?

Date that are being in Date Slicer is from "Date Table", right ?