The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Good morning,
I have a doubt and I'm not getting over it. So it's like this:
I want to calculate the number of consecutive days when the temperature is 34 or above.
This is an example of my data:
Data | Max of Temperatura [ºC] |
01/01/2020 | 15,8 |
02/01/2020 | 13,4 |
03/01/2020 | 13 |
04/01/2020 | 13,2 |
05/01/2020 | 14 |
06/01/2020 | 14,8 |
07/01/2020 | 16 |
08/01/2020 | 15,6 |
09/01/2020 | 16 |
10/01/2020 | 12,3 |
11/01/2020 | 13,7 |
12/01/2020 | 14,1 |
13/01/2020 | 11,2 |
14/01/2020 | 9 |
15/01/2020 | 13,9 |
16/01/2020 | 13,8 |
17/01/2020 | 13,6 |
18/01/2020 | 15 |
I wanted it to be a DAX formula.
I've already written a formula to give me the total number of days the temperature is equal to or greater than 34:
COUNTX(
FILTER(
ADDCOLUMNS(
Table1;
"Consecutive";
IF(
[Max of Temperature [ºC]] >= 34;
1;
0
)
);
[Consecutive] = 1
);
Table1[Data]
)
Now I would like consecutive days to be counted.
Thanks in advance for the help.
Solved! Go to Solution.
some_bih,
I've come up with a solution in the meantime. It's clearly not the most intuitive, but it was the possible one.
some_bih, thank you for your precious help.
To count consecutive days with temperature ≥34°C in DAX, group the days into streaks using date differences, then find the max streak length.
Also, check this quick guide: 180 c to f oven.
Hi @sara11 check Excel file in Power Pivot on link. Definition of consecutive day is that at least one day and previous have temperature 38,4 or above, adjust measure to your need like 34 as data in my file are partially dummy. If helps give kudos and accept solution for other.
https://1drv.ms/x/s!AlrkFTmXWup1jmAYXJCsSkOxQdcC?e=8VmUU7
Proud to be a Super User!
some_bih,
thank you so much for your help. Truly. I forgot to mention in my post (sorry) that I want to count only if it's at least 6 consecutive days, something like this:
Row Labels | SUM Temp | Check |
01/01/2020 | 15,80 | FALSE |
02/01/2020 | 13,40 | FALSE |
03/01/2020 | 13,00 | FALSE |
04/01/2020 | 13,20 | FALSE |
05/01/2020 | 38,40 | TRUE |
06/01/2020 | 39,40 | TRUE |
07/01/2020 | 40,40 | TRUE |
08/01/2020 | 41,40 | TRUE |
09/01/2020 | 42,40 | TRUE |
10/01/2020 | 43,40 | TRUE |
11/01/2020 | 13,70 | FALSE |
12/01/2020 | 14,10 | FALSE |
13/01/2020 | 40,40 | FALSE |
14/01/2020 | 41,40 | FALSE |
15/01/2020 | 42,40 | FALSE |
16/01/2020 | 43,40 | FALSE |
17/01/2020 | 2,00 | FALSE |
18/01/2020 | 16,00 | FALSE |
Can you help me?
Thank you very much.
Hi @sara11 Power Pivot is not so rich with functions as Power BI especially in Excel like overiview with simple TRUE /FALSE. Still, please implement new measure which count number of consecutive days with conected measure. If helps give kudos and accept solution for other.
#DaysAboveThreshold=
This solution is based on https://community.fabric.microsoft.com/t5/Desktop/Count-Consecutive-Days-Worked/td-p/2052307?lightbo...
Proud to be a Super User!
some_bih,
once again, thank you very much for your help. I have been working on what you shared with me, but unfortunately it is still not enough to get what I want. I don't know how to proceed next. That is, I then have to count the days when #DaysAboveTreshold is 34 or above on at least 6 consecutive days. In your formula, it counts all days, not just the at least 6 consecutive days.
In the example you gave me, I should have in the end, a total of 6 days. That would be my result. I don't know if I made myself clear.
Can you help me, please?
Thank you very much.
Hi @sara11 so you would like to show only date/ s when at least 6 con.days appear above threshold? Visual, pivot is not enought?
Proud to be a Super User!
Yes. That's it. I don't understand what you mean. Like a pivot table? I mean, I want to have the total number of, at least, 6 consecutive days (could be more) that have a temperature equal or higher than 34. With these measures I'm not getting it.
In this example, it only should count the yellow values. The green ones should be considered 0, since there are not, at least, 6 consecutive days with temperatures equal or higher than 34 (only five days).
some_bih,
I've come up with a solution in the meantime. It's clearly not the most intuitive, but it was the possible one.
some_bih, thank you for your precious help.
@sara11 nice 😁 (give kudos)
Proud to be a Super User!
@sara11 , there are few solutions for continous streak
Continuous streak : https://youtu.be/GdMcwvdwr0o
Continuous Streak -https://community.powerbi.com/t5/Desktop/Need-help-in-DAX/m-p/1277302#M559393
https://community.powerbi.com/t5/Quick-Measures-Gallery/Power-BI-Continuous-Streak-One-Day-Differenc...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Continuous-Streak-With-One-Day-Break/ba-p/1...
https://community.powerbi.com/t5/Quick-Measures-Gallery/Power-BI-Continuous-Streak-One-Day-Differenc...
http://dataap.org/blog/2018/05/30/solved-microsoft-access-the-microsoft-ace-oledb-12-0-provider-is-n...
8527401866
amitchandak,
Thank you for your reply. Unfortunately, I don't know that much about this to convert to power bi. I'm working with power pivot.