Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I am trying to calculate the mean time between failures for our Service department. For a given month the formula works fine. The issue begins when I choose range of more than one month. in the slicer. I need an expression that counts the number of months within the range (assuming that it can also be across different years).
MTBF (Days) = (LASTNONBLANK('Cases'[Closed Date (A)].[Day],1)-FIRSTNONBLANK('Cases'[Closed Date (A)].[Day],1)+1)/COUNT('Cases'[Case ID])*(MONTH(LASTNONBLANK('Cases'[Closed Date (A)].[Date],1)-MONTH(FIRSTNONBLANK('Cases'[Closed Date (A)].[Date],1)+1)))
Solved! Go to Solution.
Hi @arthurr,
# of days = DATEDIFF ( MIN ( 'Cases'[Closed Date (A)] ), MAX ( 'Cases'[Closed Date (A)] ), DAY )
Regards,
Yuliana Gu
Hi @arthurr,
It looks like you placed wrong parentheses in above formula. Please modify it to:
MTBF (Days) = ( LASTNONBLANK ( 'Cases'[Closed Date (A)].[Day], 1 ) - FIRSTNONBLANK ( 'Cases'[Closed Date (A)].[Day], 1 ) + 1 ) / COUNT ( 'Cases'[Case ID] ) * ( MONTH ( LASTNONBLANK ( 'Cases'[Closed Date (A)].[Date], 1 ) ) - MONTH ( FIRSTNONBLANK ( 'Cases'[Closed Date (A)].[Date], 1 ) ) + 1 )
Besides, to calculate time intervals between two date/time values, you can use DATEDIFFfunction.
MTBF (Days) = DATEDIFF ( MIN ( 'Cases'[Closed Date (A)] ), MAX ( 'Cases'[Closed Date (A)] ), DAY ) / COUNT ( 'Cases'[Case ID] ) * DATEDIFF ( MIN ( 'Cases'[Closed Date (A)] ), MAX ( 'Cases'[Closed Date (A)] ), MONTH )
Best regards,
Yuliana Gu
Hi,
Thank you, but I have now a better understanding of my issue. I have a date slicer for
'Cases'[Closed Date (A)]
and I would like to calculate the # of days between the 2 selections in the slicer. Issue is, that in my dataset there are no neccesseraly values in all dates. Is there a way I can use the dta points from the slicer?
Thanks
Hi @arthurr,
# of days = DATEDIFF ( MIN ( 'Cases'[Closed Date (A)] ), MAX ( 'Cases'[Closed Date (A)] ), DAY )
Regards,
Yuliana Gu
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |