# Power Bi how to make continuous exceed 4 days (positive), will automatic show total Positive days

#### Oscarr

##### Member
Hi all

At Power Bi how to make it?

If the customer have positive for continuous 4 days, will automatic show how many days.No matter on which day, as long as continuous 4 days.

example 1: kent continuous already exceed 4 days (positive) , he total got 6 days (positive). then automatic show me 6 days...

example 2: ABBY total got 5 days (positive), but she is not continuous 5 days (positive), so just show me 0

Thanks

#### Attachments

• 10.5 KB Views: 4

#### Chihiro

##### Excel Ninja
One way to do it...

1. Query the table, add copy of the table but only for Name column and remove duplicates.
2. Add Dates table starting from earliest day on original table to last date on original table.
Code:
``= List.Dates(#date(2019,5,1),7,#duration(1,0,0,0))``
3. Do Cross Join (Cartesian Join) of distinct name and date table and expand (name it Expanded).
Code:
``Custom = Table2``
4. Left Outer Join original table into Expanded using [Date] & [Name] as key, expand value column.
5. Load to Model.
6. Add calculated column to Expanded.
Code:
``````PosInRow =
VAR PrevNeg =
CALCULATE (
MAX ( Expanded[Date] ),
FILTER (
ALLEXCEPT ( Expanded, Expanded[Name] ),
Expanded[Date] < EARLIER ( Expanded[Date] )
&& Expanded[Value] < 0
&& Expanded[Value] <> BLANK ()
)
)
VAR SeriesStart =
CALCULATE (
MIN ( Expanded[Date] ),
FILTER (
ALLEXCEPT ( Expanded, Expanded[Name] ),
Expanded[Date] > PrevNeg
&& Expanded[Value] > 0
&& Expanded[Value] <> BLANK ()
)
)
RETURN
IF (
AND ( Expanded[Value] <> BLANK (), Expanded[Value] > 0 ),
DATEDIFF ( SeriesStart, Expanded[Date], DAY ) + 1
)``````
Code:
``````MaxPosInRow =
IF ( MAX ( Expanded[PosInRow] ) > 4, MAX ( Expanded[PosInRow] ), 0 )``````
8. Add [Name] & [MaxPosInRow] to table/matrix visual.

I haven't checked with more extensive data, for integrity. But you should have enough of concept to expand on it as needed.

Last edited:

#### Oscarr

##### Member
One way to do it...

1. Query the table, add copy of the table but only for Name column and remove duplicates.
2. Add Dates table starting from earliest day on original table to last date on original table.
Code:
``= List.Dates(#date(2019,5,1),7,#duration(1,0,0,0))``
3. Do Cross Join (Cartesian Join) of distinct name and date table and expand (name it Expanded).
Code:
``Custom = Table2``
4. Left Outer Join original table into Expanded using [Date] & [Name] as key, expand value column.
5. Load to Model.
6. Add calculated column to Expanded.
Code:
``````PosInRow =
VAR PrevNeg =
CALCULATE (
MAX ( Expanded[Date] ),
FILTER (
ALLEXCEPT ( Expanded, Expanded[Name] ),
Expanded[Date] < EARLIER ( Expanded[Date] )
&& Expanded[Value] < 0
&& Expanded[Value] <> BLANK ()
)
)
VAR SeriesStart =
CALCULATE (
MIN ( Expanded[Date] ),
FILTER (
ALLEXCEPT ( Expanded, Expanded[Name] ),
Expanded[Date] > PrevNeg
&& Expanded[Value] > 0
&& Expanded[Value] <> BLANK ()
)
)
RETURN
IF (
AND ( Expanded[Value] <> BLANK (), Expanded[Value] > 0 ),
DATEDIFF ( SeriesStart, Expanded[Date], DAY ) + 1
)``````
Code:
``````MaxPosInRow =
IF ( MAX ( Expanded[PosInRow] ) > 4, MAX ( Expanded[PosInRow] ), 0 )``````
8. Add [Name] & [MaxPosInRow] to table/matrix visual.

View attachment 60329

I haven't checked with more extensive data, for integrity. But you should have enough of concept to expand on it as needed.
@Chihiro Thanks for help...

I'm stuck at number3....can you Attach the Power Bi sample file to me?

Here you go.

#### Attachments

• 44.2 KB Views: 8

#### Oscarr

##### Member
Here you go.
Hi @Chihiro
your formula works.. Thanks

and may i know if i want to count negative value, how to modify the code?

#### Chihiro

##### Excel Ninja
See where Expanded[Value] is used in filter condition in two variables (VAR) and Return.

Reverse direction (i.e. you check for previous positive and min of negative value date for series start).

Edit: Added "Return" portion.

Last edited:

#### Oscarr

##### Member
See where Expanded[Value] is used in filter condition in two variables (VAR).

Reverse direction (i.e. you check for previous positive and min of negative value date for series start).
i Reverse this 2 side also cannot...

#### Attachments

• 16.6 KB Views: 10

#### Chihiro

##### Excel Ninja
You haven't done it for first VAR.

#### Chihiro

##### Excel Ninja
Code:
``````VAR PrevNeg =
CALCULATE (
MAX ( Expanded[Date] ),
FILTER (
ALLEXCEPT ( Expanded, Expanded[Name] ),
Expanded[Date] < EARLIER ( Expanded[Date] )
&& Expanded[Value] < 0
&& Expanded[Value] <> BLANK ()
)
)``````
You didn't reverse sign for Expanded[Value] < 0 here. Should be >. I'd also rename VAR as PrevPos.

#### Oscarr

##### Member
Code:
``````VAR PrevNeg =
CALCULATE (
MAX ( Expanded[Date] ),
FILTER (
ALLEXCEPT ( Expanded, Expanded[Name] ),
Expanded[Date] < EARLIER ( Expanded[Date] )
&& Expanded[Value] < 0
&& Expanded[Value] <> BLANK ()
)
)``````
You didn't reverse sign for Expanded[Value] < 0 here. Should be >. I'd also rename VAR as PrevPos.
change already still cannot...

#### Attachments

• 16 KB Views: 3

#### Chihiro

##### Excel Ninja
... this time you didn't change the sign for second VAR and the Return.

I'd recommend breaking out each var into to a new column and see what it does to debug.

#### Oscarr

##### Member
... this time you didn't change the sign for second VAR and the Return.

I'd recommend breaking out each var into to a new column and see what it does to debug.
like this?

#### Attachments

• 16.2 KB Views: 3

#### Chihiro

##### Excel Ninja
... and the Return calculation...

#### Oscarr

##### Member
Hi @Chihiro

got some problem here, when i key PosInRow all code click enter, then show this (There's not enough memory to complete this operation. Please try again later when there may be more memory available.)..

#### Attachments

• 3 KB Views: 3

#### Chihiro

##### Excel Ninja
I can't help you with that. It just means your pc doesn't have enough RAM to perform calculation.

You can try doing similar calculation in query editor using M language. Or using native query during import stage.

#### Oscarr

##### Member
I can't help you with that. It just means your pc doesn't have enough RAM to perform calculation.

You can try doing similar calculation in query editor using M language. Or using native query during import stage.
@Chihiro

i don know how to make calculation in query editor using M language and native query during import stage , can you help make a sample give me?

Thanks

#### Chihiro

##### Excel Ninja
I'm busy, so I'll give you a hint.

1. Sort by name & date.
Code:
``= Table.Sort(#"Changed Type",{{"Name", Order.Ascending},{"Date", Order.Ascending}})``
2. Add index column.

3. Add custom column to flag when it's last date of each contiguous positive value for the person (using index column added previously)
Refer to link for how to use index column to look at previous row value.

4. Select the custom column and fill up. Then group by & count rows.

#### Oscarr

##### Member
I'm busy, so I'll give you a hint.

1. Sort by name & date.
Code:
``= Table.Sort(#"Changed Type",{{"Name", Order.Ascending},{"Date", Order.Ascending}})``
2. Add index column.

3. Add custom column to flag when it's last date of each contiguous positive value for the person (using index column added previously)
Refer to link for how to use index column to look at previous row value.

4. Select the custom column and fill up. Then group by & count rows.
start number 1 i already stuck there, so confusing...need your help to make a sample send to me..

#### Oscarr

##### Member
I'm busy, so I'll give you a hint.

1. Sort by name & date.
Code:
``= Table.Sort(#"Changed Type",{{"Name", Order.Ascending},{"Date", Order.Ascending}})``
2. Add index column.

3. Add custom column to flag when it's last date of each contiguous positive value for the person (using index column added previously)
Refer to link for how to use index column to look at previous row value.

4. Select the custom column and fill up. Then group by & count rows.
hi @Chihiro

can you help Attach the Power Bi sample file to me? i still cant make it

#### Chihiro

##### Excel Ninja
Here's quick sample.

#### Attachments

• 57.4 KB Views: 2

#### Oscarr

##### Member
Here's quick sample.
hi @Chihiro

can this formula have date to choose?

like i choose 1/2/2020 to 10/2/2020

#### Chihiro

##### Excel Ninja
It's not formula really. Just transformation steps using some functions . You can easily adopt to any date ranges using filter on date colum.

#### Oscarr

##### Member
It's not formula really. Just transformation steps using some functions . You can easily adopt to any date ranges using filter on date colum.
@Chihiro
need you give me a Power Bi sample file ...