Rule based calcs in MS BI

[kenblack]

I have a challenging puzzle which I am trying to resolve I have a data set where I am trying to apply the followimg rules to count discharges from a tank

spill counting starts when first discharge occurs

• any discharge(s) in the first 12 hour block is counted as 1 spill

• any discharge(s) in the next and subsequent 24 hour blocks are each counted as 1 additional spill per block

• this counting continues until there is a 24 hour block with no discharge.

• for the next discharge after 24 hour block with no discharge, the sequence begins again.

The data set is at the end of the following link I hope it works

https://nwgcloud-my.sharepoint.com/:x:/r/personal/ken_black_nwl_co_uk/_layouts/15/Doc.aspx?sourcedoc...

Link to one drive 

I am fairly new to the BI environment but would really appreciate any help with this just to get me ...

Thanks in advance

Ken 

[MFelix]

Hi @kenblack ,

The link you have shared is asking for password can you share it without password please.

[kenblack]

https://onedrive.live.com/edit.aspx?cid=35fcf50beb555c0c&page=view&resid=35FCF50BEB555C0C!2223&parId...

[MFelix]

Hi @kenblack ,

It's still aqsking for password, can you share it trhoug a wetransfer link please.

[kenblack]

https://onedrive.live.com/edit.aspx?cid=35fcf50beb555c0c&page=view&resid=35FCF50BEB555C0C!2223&parId...

https://onedrive.live.com/edit.aspx?cid=35fcf50beb555c0c&page=view&resid=35FCF50BEB555C0C!2223&parId...