cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

Count rows with filters from another table

Good Morning,

I have a problem and I don't know how to solve it.

I have 2 tables, the first with all the production data and the second with a single column where there are the days that are holidays and weekends. The days that are working days do not appear in that second table.

In the first table I have 2 fields that are dated, the first the Date when the order was placed, and the other when the product must be delivered to the customer.

The goal of everything, is that I have to get to know the number of working days between the 2 dates.

I already have the part of the difference between the dates, but I don't know how to subtract the number of Holidays that come from the other Table.
I have tried it with the COUNTROWS and FILTER functions, but I cannot solve it ...

Could you help me?

Thanks and greetings,

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User II
Super User II

Re: Count rows with filters from another table

Do you also have a DimDate table? You will need one for this problem, then you just need to relate the Holidays to the date table, and FILTER the DimDate for Working Days and do a COUNTROWS on that in combination with the DATEDIFF.

 


______________


Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos.


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


excelwithallison.com

View solution in original post

Highlighted
Solution Sage
Solution Sage

Re: Count rows with filters from another table

// T1 with prod data: OrderDate, DeliveryDate
// T2 with hols and weekends: ExclusionDate

[Num Of Working Days] = // calculated column in T1
var __startDate = T1[OrderDate]
var __endDate = T1[DeliveryDate]
var __holidayAndWeekendCount =
	COUNTROWS(
		FILTER(
			T2,
			T2[ExclusionDate] >= __startDate
			&&
			T2[ExclusionDate] <= __endDate
		)
	)
var __result =
	__endDate - __startDate
		+ 1 - __holidayAndWeekendCount
return
	__result

 

Best

D

View solution in original post

2 REPLIES 2
Highlighted
Super User II
Super User II

Re: Count rows with filters from another table

Do you also have a DimDate table? You will need one for this problem, then you just need to relate the Holidays to the date table, and FILTER the DimDate for Working Days and do a COUNTROWS on that in combination with the DATEDIFF.

 


______________


Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos.


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


excelwithallison.com

View solution in original post

Highlighted
Solution Sage
Solution Sage

Re: Count rows with filters from another table

// T1 with prod data: OrderDate, DeliveryDate
// T2 with hols and weekends: ExclusionDate

[Num Of Working Days] = // calculated column in T1
var __startDate = T1[OrderDate]
var __endDate = T1[DeliveryDate]
var __holidayAndWeekendCount =
	COUNTROWS(
		FILTER(
			T2,
			T2[ExclusionDate] >= __startDate
			&&
			T2[ExclusionDate] <= __endDate
		)
	)
var __result =
	__endDate - __startDate
		+ 1 - __holidayAndWeekendCount
return
	__result

 

Best

D

View solution in original post

Helpful resources

Announcements
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!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors