Forum Discussion
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 | 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))
)
)
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
6 Replies
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 - _suumSubtract this with the measure you have created below Days Off.
Regards,
Harsh NathaniDid I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
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
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