How can I write a DAX measure to calculate sales that occurred on the 2nd day of 2 consecutive sunny days?
Date table has Date and Weather columns (Sunny, Cloudy, Rain, ...) linked to the Sales table by Date and Sale Date.
Northwind Sales table is fine - multiple stores, products, Sale Dates.
Let's say that Sun-Mon are sunny, Tue cloudy, Wed-Fri sunny, Sat cloudy. The customer wants to be able to distinguish between Monday, which is the 2nd day of a set of days where only 2 sunny days occurred, and Thursday, which was the 2nd consecutive day of sun but it was in the middle of a set of 3 sunny days, which would be its own category. Friday is also a sunny day after a sunny day, but that's the 3rd day of sun.
I suspect the answer is a VAR using ADDCOLUMNS but I can't get it to work.
Date table has Date and Weather columns (Sunny, Cloudy, Rain, ...) linked to the Sales table by Date and Sale Date.
Northwind Sales table is fine - multiple stores, products, Sale Dates.
Let's say that Sun-Mon are sunny, Tue cloudy, Wed-Fri sunny, Sat cloudy. The customer wants to be able to distinguish between Monday, which is the 2nd day of a set of days where only 2 sunny days occurred, and Thursday, which was the 2nd consecutive day of sun but it was in the middle of a set of 3 sunny days, which would be its own category. Friday is also a sunny day after a sunny day, but that's the 3rd day of sun.
I suspect the answer is a VAR using ADDCOLUMNS but I can't get it to work.
