Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Nl90
New Member

Count the number of serviced products

So here's the problem I struggle with. 

 

We have products at our company that are being serviced till the service period is expired. 

I would like the count the number of products being serviced at any particular time (Year/Qtr/Month). 

I started doing this by counting the # of products that enter service each time period (Month/Qtr/Year) + # of products that end service each time period by the following formula: 

* I have the two start/end dates connected to a dimintion calander table (One active and One is inactive)

 

Product serviced count =CALCULATE(COUNT(Data[Id]),

                                                          USERELATIONSHIP(DimDate[Date], Data[Start Date])) +

                                     CALCULATE(COUNT(Data[Id]),

                                                           USERELATIONSHIP(DimDate[Date], Data[End Date]))

 

However how do I count the products that in service where the start and service dates are not in the time period?

For example if I pivot the the formula with Qtrs it will give me only the products that enter service or exit service withen that qtr, what about products that started service before the qtr start date and end after the qtr end date? 

 

I tried the following for those products: 

Test during:=

Var Mn = MIN(DimDate[Date])

Var Mx = MAX(DimDate[Date])

 

Return

CALCULATE(COUNT(Data[Id]), FILTER(ALLEXCEPT(Data,Data[Customer],Data[Model],Data[Region]), AND(Data[Start Date] < Mn ,Data[End Date] > Mx)))

Not seems to work.

 

Would appriciate your kind help.

 

 

1 ACCEPTED SOLUTION
Nl90
New Member

@bhanu_gautam 

Thanks bhanu gautam. 

 

I was able to slove this in the alegant way showen here: 

 

Power Bi - Count of subscriber with variable start... - Microsoft Fabric Community

 

had to do some Binary logic to wrape my heah on why it works, but it does. 

 

View solution in original post

2 REPLIES 2
Nl90
New Member

@bhanu_gautam 

Thanks bhanu gautam. 

 

I was able to slove this in the alegant way showen here: 

 

Power Bi - Count of subscriber with variable start... - Microsoft Fabric Community

 

had to do some Binary logic to wrape my heah on why it works, but it does. 

 

bhanu_gautam
Super User
Super User

@Nl90 Try using

DAX
ProductsInService :=
VAR Mn = MIN(DimDate[Date])
VAR Mx = MAX(DimDate[Date])
RETURN
CALCULATE(
COUNT(Data[Id]),
FILTER(
Data,
Data[Start Date] <= Mx && Data[End Date] >= Mn
)
)

 




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!




LinkedIn






Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.