Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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.
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
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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.
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
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
57 | |
49 | |
44 | |
18 | |
18 |