Forum Discussion
Conditional sum: Consecutive date and non-consecutive date
Hi expert,
Scenario:
I have a report that needs me to calculate the frequency of absenteeism impact per employee. The condition is, 1 date will be count as 1, and consecutive date will also be count as 1. If the consecutive date involve 2 different week, then it will be count as 2 and not 1. If 1 date (non-consecutive) and a consecutive dates appear on the same week, the sum of frequency will be 2.
Problem (please refer below image as a reference):
Problem appear when I need to calculate the frequency of the absenteeism where on the same week (in below case is week 20) I have 2 types of absenteeism. First is non-consecutive date, second is consecutive dates. The way I calculated now will not return 2 in sum, but it will only return 1 since my code is -> DISTINCTCOUNT(Week). Can somebody please guide me on how I can tackle this issue? Thanks in advance! 😊
11 Replies
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
https://1drv.ms/u/s!AiUZ0Ws7G26RiUi13Bf9jaud2VWf?e=ZiiHhm
Hi Ahmedx ,
Thank you for your sharing. I learn new things today. 😊
But in my case this didn't work since my table have multiple employee for 1 date:Below is my measure for the offset:
When I tried, an error message popup "offset relation parameter contains column "DateStamp" which matches more than one row or filter context". Is the issue happen because of that? Thank you for your help 🙏🏻
are you trying to create a calculated column?
Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216Sorry for the delay,
Now I attached an example file from pbix. 😊
https://www.sendbig.com/view-files/?Id=d1066bf8-70e4-ae8b-0b06-20178a8509adThe sumx shows weird value.
