Count dates between two dates excluding dynamic days

[flaviocarvalho]

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:

id	DataInicio	DataFim	Monday	Tuesday	Wednesday	thursday	friday	saturday	sunday	DaysOff
1	23/05/2019	30/05/2019	False	False	False	True	True	True	False	4
2	18/05/2019	01/06/2019	False	False	True	True	True	True	False	9

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))
    )
)

[harshnathani]

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)

[flaviocarvalho]

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

[v-deddai1-msft · 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 ) )

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

[flaviocarvalho]

@v-deddai1-msft ,

Fantastic, it works perfectly, thank you very much.

Best regards.

[v-deddai1-msft]

Hi @flaviocarvalho ,

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

Best Regards,

Dedmon Dai

[flaviocarvalho]

@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