Forum Discussion
DAX average days without plant contamination
Hello AndreeaPopescuX,
Thank you for sharing your scenario and expected outcome.I have reproduced your scenario in Power BI Desktop using your sample logic for consecutive contamination-free days (EB_COUNTER) and the EB flag. By following your requirements, I set up the table and constructed the necessary DAX calculations. I confirm that the average days without contamination result is as you described: 17.5.
For your reference, I have attached the .pbix file so you can review the full setup, including the sample data and DAX measure.
If I misunderstand your needs or you still have problems on it, please feel free to let us know.
Best regards,
Ganesh Singamshetty.
Super UserWhat is your expected result given the same sample data posted?
Hi danextian ! My expected output would be: (58+7+1+4+2)/5 so the maximum number of days/ number of completed cycles. Thank you!
v-ssriganeshCommunity Support
Hi AndreeaPopescuX,
Thank you for reaching out to the Microsoft fabric community forum.Based on the data provided in your screenshot, I’ve analyzed the possibility of achieving an average of 14.4 days without contamination as per your expected output ((58 + 7 + 1 + 4 + 2) / 5 = 14.4).
However, this calculation assumes five completed cycles of contamination-free days, with the maximum EB_COUNTER values being 58, 7, 1, 4, and 2. Upon reviewing your data:
- The cycle ending on 6/29/2020 has a maximum EB_COUNTER of 58.
- The cycle ending on 7/10/2020 has a maximum EB_COUNTER of 7.
- The cycle ending on 7/15/2020 has a maximum EB_COUNTER of 1.
- The cycle ending on 7/23/2020 has a maximum EB_COUNTER of 4.
- The data shows an incomplete cycle after 7/23/2020 with a maximum EB_COUNTER of 2 (up to 7/21/2020), but it does not end with EB = 1, indicating it’s not a completed cycle.
In Power BI, the average is typically calculated based on completed cycles (where EB = 1 marks the end). With only four completed cycles (58, 7, 1, 4), the average would be (58 + 7 + 1 + 4) / 4 = 17.5 days. Including the incomplete cycle (2) to make five cycles would require assuming the cycle ends, but the data doesn’t support this as EB remains 0 after 7/23/2020.
Therefore, with the current dataset, achieving an average of 14.4 days is not possible unless additional data is provided to confirm a fifth completed cycle. If you have more recent data where EB = 1 resets the counter after 7/23/2020, please share it, and we can adjust the calculation accordingly.
Best regards,
Ganesh Singamshetty.Hello! you are correct. i should remove 2 from the calculation. the calculation should be here ( 58 + 7 + 1 + 4 ) / 4 = 17.5 - my mistake. so 4 completed cycles
- v-ssriganesh
Hello AndreeaPopescuX,
Thank you for sharing your scenario and expected outcome.I have reproduced your scenario in Power BI Desktop using your sample logic for consecutive contamination-free days (EB_COUNTER) and the EB flag. By following your requirements, I set up the table and constructed the necessary DAX calculations. I confirm that the average days without contamination result is as you described: 17.5.
For your reference, I have attached the .pbix file so you can review the full setup, including the sample data and DAX measure.
If I misunderstand your needs or you still have problems on it, please feel free to let us know.
Best regards,
Ganesh Singamshetty.