How to find consecutive days with a sale?

[condale7]

SQLBI and Patrick recently came out with a video for consecutive days without a sale (https://www.youtube.com/watch?v=GR9ROCQVyLk). Does anyone have any insights into how one would find consecutive days with a sale? I hope to make a table like below. Any insights would help! Thanks again!

Table:

Date	Sale ID	Sale	Consecutive Days with Sale
5-10-2021	1	$575	1
5-11-2021	2	$484	2
5-12-2021	3	$30	3
5-13-2021			0
5-14-2021	4	$902	1

[Jihwan_Kim · Accepted solution]

Hi, @condale7 

Please check the below picture and the sample pbix file's link down below. (creating a new measure).

I created a sample pbix file with expanded dates based on the explanation.

Sales Consecutive Days =

VAR currentdate =

MAX ( Dates[Date] )

VAR lastblankdate =

MAXX (

FILTER (

ALL ( Dates ),

Dates[Date] <= currentdate

&& ISBLANK ( [Sales Total] )

),

Dates[Date]

)

RETURN

IF (

ISFILTERED ( Dates[Date] ),

IF (

ISBLANK ( [Sales Total] ),

0,

IF (

ISBLANK ( lastblankdate ),

DATEDIFF ( CALCULATE ( MIN ( Dates[Date] ), ALL ( Dates ) ), currentdate, DAY ) + 1,

DATEDIFF ( lastblankdate, currentdate, DAY )

)

)

)

https://www.dropbox.com/s/wg4eacs3s1m2msi/condale.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[mahoneypat]

And if you needed the measure version

ConsecDays Measure =
VAR thisdate =
    MIN ( Sales2[Date] )
VAR overallmin =
    CALCULATE ( MIN ( Sales2[Date] ), ALL ( Sales2 ) )
VAR lastnosales =
    CALCULATE (
        MAX ( Sales2[Date] ),
        ALL ( Sales2 ),
        Sales2[Date] < thisdate,
        ISBLANK ( Sales2[Sale] )
    )
VAR day1 =
    IF ( ISBLANK ( lastnosales ), overallmin - 1, lastnosales )
RETURN
    IF ( ISBLANK ( MIN ( Sales2[Sale] ) ), 0, DATEDIFF ( day1, thisdate, DAY ) )

Pat

[condale7]

Hey Pat, 

I almost seem to be there and thank you so much for your help. I provided a screenshot below of the behavior in a table. As you can see, I have the Dayswith0 behaving how I want it. It counts 1,2,3 when there are no sales for three days. However, the consecutive sales DAX measure that you gave me seems to count from the first day. I have tried to manipulate the code to change this behavior but this is a little over my head to be honest. Do you know what in your code could be changed to change this behavior?

Thanks again!

Connor

[mahoneypat]

Here is a column expression that shows one way to do it.  Replace Sales2 with your actual table name.

ConsecDays =
VAR thisdate = Sales2[Date]
VAR overallmin =
    MIN ( Sales2[Date] )
VAR lastnosales =
    CALCULATE (
        MAX ( Sales2[Date] ),
        ALL ( Sales2 ),
        Sales2[Date] < thisdate,
        ISBLANK ( Sales2[Sale] )
    )
VAR day1 =
    IF ( ISBLANK ( lastnosales ), overallmin - 1, lastnosales )
RETURN
    IF ( ISBLANK ( Sales2[Sale] ), 0, DATEDIFF ( day1, thisdate, DAY ) )

Pat