consecutive Days where value is 0

[Matt65]

I am attempting to calculate how many consecutive days, by location, where the amount equals 0. 

Below is sample data, I have a date column, a text location column, an amount column (which can include negative amounts, which when encountered should be treated as 0), and I have added a Days of 0 column which is the expected output for this sample data.

I have found https://community.powerbi.com/t5/DAX-Commands-and-Tips/calculate-running-total-of-consecutive-value/... which is similar to what I am wanting, except their wanting the 0's to break the consecutive count and I am wanting to only count consecutive 0's by location.

Using the expression from the above topic, I am getting 0's on the days where amount=0, but cannot modify the expression to get it to work how I am wanting. 

Days of Zero = 
var _date = 'Table'[Date]
var _allResource = ALLEXCEPT('Table', 'Table'[Location])
var _firstZero = CALCULATE(MAX('Table'[Date]), _allResource, 'Table'[Date] <= _date, 'Table'[Amount]>0)
var _start = IF(ISBLANK(_firstZero), CALCULATE(MIN('Table'[Date]), _allResource), _firstZero)

RETURN
CALCULATE(SUM('Table'[Amount]), FILTER(ALL('Table'[Date]), 'Table'[Date] >= _start && 'Table'[Date] <= _date))

Attached is a sample file.

Sample-Consecutive Zeros by Location.pbix 

[TomMartens]

Hey @Matt65 ,

I'm wondering why the 6th of May is counted 3 for location 1 (or A), as there is no value available in the sample data for the 5th of May.

Does this mean missing data has to be considered as a zero-valued event in the sequence of consecutive zero-valued days?

I assume that the 1st zero-valued day will be after the 1st data point available for a sequence (faceted by location)?
This question may sound weird, but what I try to understand is this: Assuming there is a location D with a single record in the dataset, date equals 2020-05-04 and amount equals 0, is this the beginning of the counting meaning a measure will return 1 or 4 because the 1st of May is the MINIMUM date for other locations.

My last question 🙂
Assuming there is a location D with a single record in the dataset, date equals 2020-05-04 and amount equals 0, is this also the end of the sequence or does the sequence stops on the 7th of May because May 7th is the MAXIMUM date for the other locations.

Regards,

Tom

[Matt65]

Hi Tom, good find on my poor data sample.  Appologies, the dates should have been consecutive.

To answer your questions, if a new location was added part way through the data period and had a 0 then that would be the first occurence and so would have a 1, the data would be consecutive but if there was a break then it should be treated as 0's.  I've corrected my example PBIX and sample image: 

[mahoneypat · Accepted solution]

Please try this approach.

Consecutive Zeros =
VAR thisdate = Data[Date]
VAR lastnonzero =
CALCULATE (
MAX ( Data[Date] ),
FILTER ( ALLEXCEPT ( Data, Data[Location] ), Data[Date] <= thisdate ),
Data[Amount] <> 0
)
RETURN
IF (
Data[Amount] = 0,
IF ( ISBLANK ( lastnonzero ), 1, DATEDIFF ( lastnonzero, thisdate, DAY ) ), 0
)

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Matt65]

Hi Pat,

Thanks very much, that has in fact worked.  Thanks very much for your time in helping me with this problem.

Matt