Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Need Help! Calculations based on Date Filters

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!

Need Help
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    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.

     

     

5 Replies

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    6 years ago

    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.

     

     

  • FarhanAhmed's avatar
    FarhanAhmed
    Community Champion
    6 years ago

    Can you please share some sample data and required results?

     

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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 IDStart DateConsidered Start DateFinish DateConsidered Finish DateExpected Measure Value
    15/15/20205/15/20205/20/20205/20/20205
    Scenario 2    0
    Row IDStart DateConsidered Start DateFinish DateConsidered Finish DateExpected Measure Value
    24/15/20205/1/20205/20/20205/20/202019
    Scenario 3     
    Row IDStart DateConsidered Start DateFinish DateConsidered Finish DateExpected Measure Value
    35/15/20205/15/20206/20/20205/31/202016
    • FarhanAhmed's avatar
      FarhanAhmed
      Community Champion
      6 years ago
      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.