Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
sara11
Helper I
Helper I

DAX power pivot formula to calculate the number of consecutive days when the temperature is above 34

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/202015,8
02/01/202013,4
03/01/202013
04/01/202013,2
05/01/202014
06/01/202014,8
07/01/202016
08/01/202015,6
09/01/202016
10/01/202012,3
11/01/202013,7
12/01/202014,1
13/01/202011,2
14/01/20209
15/01/202013,9
16/01/202013,8
17/01/202013,6
18/01/202015

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.

 

1 ACCEPTED 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.

1.png

3.png2.png

some_bih, thank you for your precious help.

View solution in original post

11 REPLIES 11
alaricjude01
New Member

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.

some_bih
Super User
Super User

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 

some_bih_0-1687205528271.png

 





Did I answer your question? Mark my post as a solution!

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 LabelsSUM TempCheck
01/01/202015,80FALSE
02/01/202013,40FALSE
03/01/202013,00FALSE
04/01/202013,20FALSE
05/01/202038,40TRUE
06/01/202039,40TRUE
07/01/202040,40TRUE
08/01/202041,40TRUE
09/01/202042,40TRUE
10/01/202043,40TRUE
11/01/202013,70FALSE
12/01/202014,10FALSE
13/01/202040,40FALSE
14/01/202041,40FALSE
15/01/202042,40FALSE
16/01/202043,40FALSE
17/01/20202,00FALSE
18/01/202016,00FALSE


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= 

VAR _current_row=MAX(Table2[Date])
VAR _last_threshold=
MAXX(
FILTER(ALL(Table1); Table1[Data]<= _current_row && [Check_v2]=FALSE); Table1[Data]
)
VAR _diff=( _current_row- _last_threshold )*1
RETURN _diff
 
Connected measure Check_v2=
VAR _current_tem=[SUM Temp]
VAR _threshold=38,4
VAR _Result=
IF(_current_tem>=_threshold;TRUE;FALSE)
RETURN _Result



some_bih_0-1687260249435.png

 

This solution is based on https://community.fabric.microsoft.com/t5/Desktop/Count-Consecutive-Days-Worked/td-p/2052307?lightbo... 





Did I answer your question? Mark my post as a solution!

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?





Did I answer your question? Mark my post as a solution!

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. 

1.png

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.

1.png

3.png2.png

some_bih, thank you for your precious help.

@sara11 nice 😁 (give kudos)





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






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.

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors