The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
Solved! Go to Solution.
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.
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.
@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
)
)
Proud to be a Super User! |
|
User | Count |
---|---|
80 | |
74 | |
41 | |
30 | |
28 |
User | Count |
---|---|
107 | |
96 | |
53 | |
47 | |
47 |