Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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: id DataInicio DataFim...
Need Help
  • v-deddai1-msft's avatar
    v-deddai1-msft
    6 years ago

    Hi Anonymous ,

     

    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

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Anonymous ,

 

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)

 

 

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Anonymous ,

 

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's avatar
    v-deddai1-msft
    Community Support
    6 years ago

    Hi Anonymous ,

     

    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

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      v-deddai1-msft ,

       

      Fantastic, it works perfectly, thank you very much.

       

      Best regards.

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

        Hi Anonymous ,

         

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

         

        Best Regards,

        Dedmon Dai