Forum Discussion

BilboBaggins's avatar
BilboBaggins
Frequent Visitor
8 years ago
Solved

Days between based on slicer

Greetings!

I have table that has several lines of startdate and enddate. I can easily calculate a new colun that shows the number of days between startdate and enddate, but in the next step I'm bit lost.

I have added a date table, and made a slicer based on that. I want to count the days between startdate and enddate so that it is dependent on slicer. Here's an example 

startdate = 1.1.2018
enddate = 1.12.2018
slicer start = 1.2.2018
slicer end = 31.12.2018

Based on those dates, I would like to have a new column/measure, that would give me number of days between 1.2.2018 and 1.12.2018. 

 

I have tried to do this for example with following:

 

DayCount2 = 
VAR start = person[startDate]
VAR end = person[endDate]
RETURN
CALCULATE(
    DATEDIFF(
        IF(start < MIN('Date'[Date]);MIN('Date'[Date]);start);
        IF(end > MAX('Date'[Date]);MAX('Date'[Date]);end);
        DAY)
)

However, with this, MIN() and MAX() always gives the first and last date from the date table. I tried to do a simple measure with them, and with them I get the right result from slicer. 

 

Thank you in advance!

  • Anonymous's avatar
    Anonymous
    8 years ago

    BilboBaggins,

    You can create the following measure. For more details, please check attached PBIX file.

    Datediff = var maxslicer=MAX('Date'[Date]) var minslicer=MIN('Date'[Date]) return CALCULATE(
        DATEDIFF(
            IF(MAX(person[startDate]) < minslicer;minslicer;MAX(person[startDate]));
            IF(MAX(person[endDate]) > maxslicer;maxslicer;MAX(person[endDate]) );
            DAY)
    )



    Regards,
    Lydia

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    BilboBaggins,

    You can create the following measure. For more details, please check attached PBIX file.

    Datediff = var maxslicer=MAX('Date'[Date]) var minslicer=MIN('Date'[Date]) return CALCULATE(
        DATEDIFF(
            IF(MAX(person[startDate]) < minslicer;minslicer;MAX(person[startDate]));
            IF(MAX(person[endDate]) > maxslicer;maxslicer;MAX(person[endDate]) );
            DAY)
    )



    Regards,
    Lydia