Conditional sum: Consecutive date and non-consecutive date

[New_be]

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! 😊 

[New_be]

Sorry for the delay,

Now I attached an example file from pbix. 😊
https://www.sendbig.com/view-files/?Id=d1066bf8-70e4-ae8b-0b06-20178a8509ad 

The sumx shows weird value.

[New_be]

Hi @Ahmedx , I already attached pbix file for your reference.

[Ahmedx · Accepted solution]

pls try this

https://1drv.ms/u/s!AiUZ0Ws7G26RiVs3N2tYGq_Fvyyt?e=NT4v85

[New_be]

Hi @Ahmedx ,

Thank you so much! It solved the issue. In my case I just need another summarize function inside sum. Bless you!

[Ahmedx]

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

[New_be]

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 🙏🏻

[Ahmedx]

are you trying to create a calculated column?

[New_be]

Hi Ahmedx,
No, the image i attached is measure.. I tried to copy from what you did in your pbix file that I downloaded..

[Ahmedx]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot).

[New_be]

I created a simple pbix and I tested your code. Your code is 100% is working! 😊

But, in the real data that I have, when "DATEDIFF([Max date], [Gap in date],DAY) + 0" code makes my visual table weird. If only "DATEDIFF([Max date], [Gap in date],DAY)" its work. See below image:

I have filter outside visual which is EmpNo = 18161 and YearMonth = Y23M05. When I used  "DATEDIFF([Max date], [Gap in date],DAY) + 0", it ignore the filter outside. This is the only issue that i have left.

Relationship & measure:

Filter EmpNo that I filter outside coming from table profile & occupation.

Thanks again for your assistance. Appreciate it

[lbendlin]

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-...