Rolling Count that resets when a row value is zero

[Uka]

Hello ,

Please could you help me to calculate the rolling count of a column (~Category1 No) in AGG_Worked_Hours table; such that when the value is 1, it will do a rolling count but once the value become 0, it will reset count to 0 and start counting the rolling again. This process would continue as a loop untill the end of the data.

This should be able to return the rolling count of each row as below when an Employee_ID is selected from Dim_Employee table.

Please can someone give me a hint on how to attched the pbi file and sample dataset?
Many thanks for your help! 

[Jihwan_Kim]

Hi, 

Please check the below attached pbix file.

It is quite difficult to understand the sample that you provided, but I tried to create my own sample.

I am not sure if I understood your requirement correctly, but I tried to reset calculation every zero by using the concept of PARTITIONBY DAX function.

1. create a partition column in the table.

2. use this partition column in WINDOW DAX function.

[Uka]

Hi @Jihwan_Kim 

Many thanks for your help!  I really appreciate; but I got an error below; 

Please can I send my pbi file for you to see?

If yes, please can you talk me through how to attach the file as I can't find the attach botton in this window.

Many thanks

[Jihwan_Kim]

Hi, 

I also created a short blog post about solving the similar problem.
Please refer to the link: How I create Cumulative Sums that resets whenever value is zero (jihwankimcscp.wixsite.com)

[Jihwan_Kim]

Hi, 

I am not sure whether you can attached the file or not, but you can also share the link of the file.

If it is OK with you, please share the link (Onedrive link or googledrive link or dropbox link), and then I can try to look into it.

Thank you.

[Uka]

Hi @Jihwan_Kim 

Many thanks once again.

I have save the file in onedrive and here is the link: https://drive.google.com/drive/folders/1UL5bBfUs79bHBdF_jymqVDYDdeH7ehyM?usp=drive_link

I have included the sample pbi file, dataset and expected output in the folder.

Thanks once again for your help!

[Jihwan_Kim]

Hi,

I cannot find any files in the folder.

Could you please check?

Thank you.

[Uka]

Hi @Jihwan_Kim 

I am not sure why you can not access it , please can you try this link: https://drive.google.com/drive/folders/1JdtxYKj-tp8kCJtoRQYs72Qc92Qcva4J?usp=sharing

Regards

Amaka

[Jihwan_Kim]

Hi, 

Please check the attached file if it works.

[Uka]

Hi @Jihwan_Kim ,

Many thanks once again!

I have also acheived the same result as you did using your blog (How I create Cumulative Sums that resets whenever value is zero (jihwankimcscp.wixsite.com))

The blog was very helpfull. Thanks once more!

But my chalenge now is to get the (cumulative sum measure with reset) to return max value in each row of the Tax_YearWeek as below;

Please can you help more?

Thanks

[Jihwan_Kim · Accepted solution]

Hi,

Please kindly check the attached file and the below picture if it suits the requirement.
Thank you.

[Uka]

Hi @Jihwan_Kim ,

You are a star!

Many thanks once again, you have saved the day!

Kind Regards