Trying to count up consecutive days a condition is met

[Liamnorris25]

Hi all, 

I am new to PowerBI and need some help with DAX I have already created a formula to do this in excel but am having issues in PowerBI. All suggestions are appreciated.  

Here is an image of what I am trying to do: 

I need to count all intervals that meet a condition and if they do, I want to count how many timestamps in a row this occurs for. 

So I need to access the cell in the same calculated column but on cell up. Here is the formula in excel (it is from a large spreadsheet

From 1->2 (image above)

=IF(PJ4>0,TJ3+1,0)       (PJ is from raw data, TJ is the data # 2 from the image above)

Again thank you for any help you may have!

[Jimmy801]

Hello @Liamnorris25 

is there still the need of help?

Jimmy

[Jimmy801]

Hello @Liamnorris25 

the approach you are using in Excel is not working in Power Query (not DAX) as it's not possible to reference a different row of the new created calculated column. This said, this task it's not that easy to solve in Power Query.

Therefore I want to make some fundamental questions to you:

- Why is this needed in Power Query? What would be the advantage? (to mess up the big Excel-sheet to have a clean process there?)

- your description deviates from the screenshot, as in the screenshot 3 transformation steps are needed but in the description your are talking only about the first.

- What would be the final goal of your process? Really the endpoint... to have the exctract shown in the last pic or do you have to evaluate it again?

BR

Jimmy

[rajulshah]

Hello @Liamnorris25,

Maybe this link could help. 

I tried with the 1A column and tried to create 2A column. See below:

Hope this helps.