DAX: Find the largest period in days of consecutive dry days.

[Bilsem]

Hi,

I'm trying to construct a pattern to find the largest period of consecutive dry days in a year.
Dataset contains: date, rain in millimeter.

Help is much appreciated.

Thanks, Ronald van Bilsem

 

[SimonNU]

The below isn't a good implementation (very iterative & the storage engine will not cache anything) but it's conceptually simple:


Add the below calculated columns:


"Previous 0 Date"

= CALCULATE (
    MAX ( [Date] ),
    FILTER (
        ALL ( Table1 ),
        Table1[Date] < EARLIER ( [Date] ) && [rain in millimeter] = 0
    )
)

"Date > 0 Before Previous 0 Date"

=
CALCULATE (
    MAX ( [Date] ),
    FILTER (
        ALL ( Table1 ),
        Table1[Date] < EARLIER ( [Previous 0 Date] ) && [rain in millimeter] > 0
    )
)

"Datediff"

= 1 * [Previous 0 Date] - [Date > 0 Before Previous 0 Date]

 

[Bilsem]

Thanks SimonNu, that helped a lot. I've solved the problem, with your help.