cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

Count dates between two dates excluding dynamic days

Hi community,


I've this table, and I want to return the count of days between two dates and exclude all "False" days (monday, or tuesday, or wednesday...).

 

Example:

idDataInicioDataFimMondayTuesdayWednesdaythursdayfridaysaturdaysundayDaysOff
123/05/201930/05/2019FalseFalseFalseTrueTrueTrueFalse4
218/05/201901/06/2019FalseFalseTrueTrueTrueTrueFalse9

 

Daysoff column:

id 1 -> 8 days - 4 days (with False) = 4 days

id 2 -> 15 days - 6 days (with False) = 9 days

 

I can get the datesbetween DataInicio and DataFim, but I need help to include this exception and get the final result.

 

 

 

DaysOff = 
var _date = xIndisponibilidades[DataInicio]
var _dateF = xIndisponibilidades[DataFim]
return
CALCULATE(
    DISTINCTCOUNT(Calendario[Date]),
    FILTER(
        ALL('Calendario'),
        'Calendario'[Date]<=DATE(YEAR(_dateF),MONTH(_dateF),DAY(_dateF))&&
        'Calendario'[Date]>=DATE(YEAR(_date),MONTH(_date),DAY(_date))
    )
)

 

 

 

1 ACCEPTED SOLUTION

Hi @flaviocarvalho ,

 

Please refer to the following measure:

 

 

Measure =
VAR days =
    DATEDIFF ( MAX ( 'Table'[DataInicio] ), MAX ( 'Table'[DataFim] ), DAY ) + 1
VAR selected_dates =
    ADDCOLUMNS (
        GENERATESERIES ( MAX ( 'Table'[DataInicio] ), MAX ( 'Table'[DataFim] ) ),
        "Weekday", WEEKDAY ( [Value], 3 )
    )
VAR mcount =
    IF (
        MAX ( 'Table'[Monday] ) = "False",
        COUNTROWS ( FILTER ( selected_dates, [Weekday] = 0 ) ),
        0
    )
VAR tcount =
    IF (
        MAX ( 'Table'[Tuesday] ) = "False",
        COUNTROWS ( FILTER ( selected_dates, [Weekday] = 1 ) ),
        0
    )
VAR wcount =
    IF (
        MAX ( 'Table'[Wednesday] ) = "False",
        COUNTROWS ( FILTER ( selected_dates, [Weekday] = 2 ) ),
        0
    )
VAR Tucount =
    IF (
        MAX ( 'Table'[thursday] ) = "False",
        COUNTROWS ( FILTER ( selected_dates, [Weekday] = 3 ) ),
        0
    )
VAR fcount =
    IF (
        MAX ( 'Table'[friday] ) = "False",
        COUNTROWS ( FILTER ( selected_dates, [Weekday] = 4 ) ),
        0
    )
VAR sacount =
    IF (
        MAX ( 'Table'[saturday] ) = "False",
        COUNTROWS ( FILTER ( selected_dates, [Weekday] = 5 ) ),
        0
    )
VAR suncount =
    IF (
        MAX ( 'Table'[sunday] ) = "False",
        COUNTROWS ( FILTER ( selected_dates, [Weekday] = 6 ) ),
        0
    )
RETURN
    ( days - ( mcount + tcount + wcount + Tucount + fcount + sacount + suncount ) )

 

 

Capture1.PNG

 

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

 

Best Regards,

Dedmon Dai

 

View solution in original post

6 REPLIES 6
Super User III
Super User III

Hi @flaviocarvalho ,

 

Create a new Column

 

This will give you the sum of False Days

 

Column =
VAR _mon =
    IF (
        'Table'[Monday]
            = TRUE (),
        1,
        0
    )
VAR _tue =
    IF (
        'Table'[Tuesday]
            = TRUE (),
        1,
        0
    )
VAR _wed =
    IF (
        'Table'[Wednesday]
            = TRUE (),
        1,
        0
    )
VAR _thur =
    IF (
        'Table'[thursday]
            = TRUE (),
        1,
        0
    )
VAR _fri =
    IF (
        'Table'[friday]
            = TRUE (),
        1,
        0
    )
VAR _sat =
    IF (
        'Table'[saturday]
            = TRUE (),
        1,
        0
    )
VAR _sun =
    IF (
        'Table'[sunday]
            = TRUE (),
        1,
        0
    )
VAR _suum = _mon + _tue + _wed + _thur + _fri + _sat + _sun
RETURN
    7 - _suum

 

 

Subtract this with the measure you have created below  Days Off.

 

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

 

 

 

Hi @harshnathani ,

 

Thanks for your reply. I Think I need a measure/column that calculate how many "Falses" I've in the period between my DataInicio and DataFim.

 

Example,

 

In my ID 2, I've 15 days (18/05/2019 - 01/06/2019) and in this 15 days, I need to calculate how many Monday, Tuesday and sunday (My falses) exists in this period.

In this period of time I've 6 matchs, because in may  the day 19 is sunday, 20 (monday), 21 (tuesday), 26 (sunday), 27 (monday) and 28 (tuesday), So then I will calculate 15-6 = 9days available

 

With your calculated column I only get how many falses I've by ID

 

Thnks,

Regards

Hi @flaviocarvalho ,

 

Please refer to the following measure:

 

 

Measure =
VAR days =
    DATEDIFF ( MAX ( 'Table'[DataInicio] ), MAX ( 'Table'[DataFim] ), DAY ) + 1
VAR selected_dates =
    ADDCOLUMNS (
        GENERATESERIES ( MAX ( 'Table'[DataInicio] ), MAX ( 'Table'[DataFim] ) ),
        "Weekday", WEEKDAY ( [Value], 3 )
    )
VAR mcount =
    IF (
        MAX ( 'Table'[Monday] ) = "False",
        COUNTROWS ( FILTER ( selected_dates, [Weekday] = 0 ) ),
        0
    )
VAR tcount =
    IF (
        MAX ( 'Table'[Tuesday] ) = "False",
        COUNTROWS ( FILTER ( selected_dates, [Weekday] = 1 ) ),
        0
    )
VAR wcount =
    IF (
        MAX ( 'Table'[Wednesday] ) = "False",
        COUNTROWS ( FILTER ( selected_dates, [Weekday] = 2 ) ),
        0
    )
VAR Tucount =
    IF (
        MAX ( 'Table'[thursday] ) = "False",
        COUNTROWS ( FILTER ( selected_dates, [Weekday] = 3 ) ),
        0
    )
VAR fcount =
    IF (
        MAX ( 'Table'[friday] ) = "False",
        COUNTROWS ( FILTER ( selected_dates, [Weekday] = 4 ) ),
        0
    )
VAR sacount =
    IF (
        MAX ( 'Table'[saturday] ) = "False",
        COUNTROWS ( FILTER ( selected_dates, [Weekday] = 5 ) ),
        0
    )
VAR suncount =
    IF (
        MAX ( 'Table'[sunday] ) = "False",
        COUNTROWS ( FILTER ( selected_dates, [Weekday] = 6 ) ),
        0
    )
RETURN
    ( days - ( mcount + tcount + wcount + Tucount + fcount + sacount + suncount ) )

 

 

Capture1.PNG

 

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

 

Best Regards,

Dedmon Dai

 

View solution in original post

@v-deddai1-msft ,

 

Fantastic, it works perfectly, thank you very much.

 

Best regards.

Hi @flaviocarvalho ,

 

Would you please consider Accept it as the solution to help the other members find it more quickly.

 

Best Regards,

Dedmon Dai

@v-deddai1-msft  , can you help me just in one last question?

 

If I have a calendar table, I get this error when I can't get any matches

 

flaviocarvalho_2-1593159631854.png

 

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors