Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello!
I'm trying to write a formula for a measured column to calculate wheter or not a repair was right at the first attempt. I have a table with tickets and columns for
- serialnumbers of products,
- dates on which an issue was reported, and
- dates on which an issue was solved.
My formula should aim to count the number of times a serial number occurs from the closing date, until 30 days later. If this is zero times, then the repairs is considered a first time right. If not (and a product malfunction is reported again for the same serial number within 30 days) then obviously it's not first time right. I can't seem to figure out how to write this IF statement.
My current attempt for a calculated column looks like this (but doesn't give the result i'm looking for):
Solved! Go to Solution.
Hi @Anonymous ,
Please try:
Column =
VAR _a =
CALCULATE (
COUNT ( 'Table'[Serialnumber] ),
FILTER ( 'Table', [Serialnumber] = EARLIER ( 'Table'[Serialnumber] ) ),
DATESINPERIOD ( 'Table'[closingdate], 'Table'[closingdate], 30, DAY )
)
VAR _b =
CALCULATE (
COUNT ( 'Table'[Serialnumber] ),
FILTER (
'Table',
[Serialnumber] = EARLIER ( 'Table'[Serialnumber] )
&& (
(
[closingdate] >= EARLIER ( 'Table'[closingdate] )
&& [closingdate]
<= EARLIER ( 'Table'[closingdate] ) + 30
)
|| (
[reportingdate] <= EARLIER ( 'Table'[reportingdate] )
&& [reportingdate]
>= EARLIER ( 'Table'[reportingdate] ) - 30
)
)
)
)
RETURN
IF (
_b = 1,
"first time right",
IF ( _a = 1, "right(not first time) ", "not right" )
)
Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
Please try:
Column =
VAR _a =
CALCULATE (
COUNT ( 'Table'[Serialnumber] ),
FILTER ( 'Table', [Serialnumber] = EARLIER ( 'Table'[Serialnumber] ) ),
DATESINPERIOD ( 'Table'[closingdate], 'Table'[closingdate], 30, DAY )
)
VAR _b =
CALCULATE (
COUNT ( 'Table'[Serialnumber] ),
FILTER (
'Table',
[Serialnumber] = EARLIER ( 'Table'[Serialnumber] )
&& (
(
[closingdate] >= EARLIER ( 'Table'[closingdate] )
&& [closingdate]
<= EARLIER ( 'Table'[closingdate] ) + 30
)
|| (
[reportingdate] <= EARLIER ( 'Table'[reportingdate] )
&& [reportingdate]
>= EARLIER ( 'Table'[reportingdate] ) - 30
)
)
)
)
RETURN
IF (
_b = 1,
"first time right",
IF ( _a = 1, "right(not first time) ", "not right" )
)
Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks!!!😀
Hi @Anonymous ,
Please try:
First time right for repairs =
VAR _a =
CALCULATE (
COUNT ( 'Table'[Serialnumber] ),
FILTER ( 'Table', [Serialnumber] = EARLIER ( 'Table'[Serialnumber] ) ),
DATESINPERIOD ( 'Table'[closingdate], 'Table'[closingdate], 30, DAY )
)
RETURN
IF ( _a = 1, "first time right", "not first time right" )
Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Yes this works! Thank you 🙂
I have an additional question.. in case of multiple repairs, the final repair will always show a first time right as the formula only looks at the future, but this is misleading. Can we build it so that the count function looks for the same serial numbers 30 days after the solve date and 30 days before the reportingdate to solve this?
Hi JamesFr06,
I have a date table linked to the reportingdate column. Please find a part of the table with some examples below. I included serialnumbers that cover all 3 possibilities (serial number occurs once -> first time right, serial number occurs twice within 30 days -> not first time right, serial number occers multiple times but with intervals greater than 30 days -> first time right). Hope this helps. Thanks for your effort!
Serialnumber | reportingdate | closingdate |
EX010058F | 27-2-2020 | 27-2-2020 |
EX0101HS7 | 29-9-2020 | 29-9-2020 |
EX0101HWH | 29-9-2020 | 29-9-2020 |
EX0105142 | 21-12-2020 | 23-12-2020 |
EX0105HNZ | 12-11-2020 | 12-11-2020 |
EX0105HNZ | 29-7-2020 | 29-7-2020 |
EX0105HP6 | 12-11-2020 | 12-11-2020 |
EX0105HPQ | 26-10-2021 | 27-10-2021 |
EX0107TDZ | 11-10-2021 | 13-10-2021 |
EX0107TE1 | 13-11-2020 | 13-11-2020 |
EX0109ZK0 | 16-9-2020 | 17-9-2020 |
EX010FNP9: | 28-7-2020 | 28-7-2020 |
EX010FNX0 | 12-11-2020 | 12-11-2020 |
EX010GGT7 | 13-8-2020 | 13-8-2020 |
EX010GGT7 | 4-8-2020 | 5-8-2020 |
EX010GGTC | 19-8-2022 | 22-8-2022 |
EX010GGTJ | 19-8-2022 | 22-8-2022 |
EX010GGTS | 28-7-2020 | 28-7-2020 |
EX010HDS6 | 12-11-2020 | 12-11-2020 |
EX010HDS6 | 29-7-2020 | 29-7-2020 |
EX010HDSP | 12-11-2020 | 12-11-2020 |
EX010JNT3 | 22-8-2022 | 22-8-2022 |
EX010LFKV | 10-8-2022 | 10-8-2022 |
EX010LJYT | 13-4-2022 | 13-4-2022 |
EX010M461 | 25-3-2022 | 29-3-2022 |
EX010M461 | 25-8-2021 | 27-8-2021 |
EX010M49Q | 28-2-2022 | 28-2-2022 |
EX010M4GC | 20-5-2022 | 20-5-2022 |
Hi JamesFr06,
Thanks for your effort! This does work in the example but when I apply it to my dataset it doesn't give the correct values anymore... i've tried the formula proposed by Jianbo Li and this worked!
Hi Rvd 18,
Do you have a D&ate table ?
And could you provide some datas ?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
57 | |
49 | |
44 | |
18 | |
18 |