Dax Formula Count Consecutive Months value is 0

[Anonymous]

Hi,

I need some help writing a DAX formula that will display the count in a new column that shows how many consecutive Measurement Months a Measured Volume is 0.5 or less for each Meter Number. My data set is 6/1/2021 - 6/31/2021 so my results should not be greater than 12. Any help would be great! 

[Anonymous]

@Greg_Deckler thanks Greg for the response. I am confused by the application of Cthulhu. Seems like a really complex formula for what I am attempting to achieve. Do you have any other examples handy? Thanks!

[Greg_Deckler]

@Anonymous Well, what you are trying to achieve is actually fairly complicated from a DAX perspective. DAX is built around the concept of aggregating and filtering data. What you want to do is build a table and then analyze row by row how things are changing, comparing each row with previous rows in order to determine when a certain condition no longer exists. In a certain sense, it is a while loop and DAX does not have while and for loops. In other words, what you are trying to achieve, DAX was never built to achieve and, in fact, looping was specifically excluded from DAX. So, you should be happy that you can do it at all.

I've written pretty extensively on this subject so here are additional examples that include while loops (Cthulhu is one and there is a PBIX you can download):

For and While Loops in DAX - Microsoft Power BI Community

TRIMMEAN - Microsoft Power BI Community

Days of Supply - Microsoft Power BI Community

While Loop - Microsoft Power BI Community

[Anonymous]

@Greg_Deckler after speaking with a colleague of mine we think we made need to stage the data better before attempting to achieve this complex formula. We also thought we just flag each meter with a "yes/no" if it is 0 volumes for 12 consecutive months. Thank you for the links! I'll keep you posted if we come up with a solution that works for our needs. 

[Greg_Deckler]

@Anonymous Seems like an application of Cthulhu. https://community.powerbi.com/t5/Quick-Measures-Gallery/Cthulhu/td-p/509739