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

Oscarr · May 27, 2019

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

Chihiro · May 27, 2019

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
    )

7. Add measure.

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.

Oscarr · May 28, 2019

Chihiro said:
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 )

7. Add measure.

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?

Chihiro · May 28, 2019

Here you go.

Oscarr · May 28, 2019

Chihiro said:
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 · May 28, 2019

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.

Oscarr · May 28, 2019

Chihiro said:
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...

Chihiro · May 28, 2019

You haven't done it for first VAR.

Oscarr · May 28, 2019

Chihiro said:
You haven't done it for first VAR.

Can write for me?

Thanks

Chihiro · May 29, 2019

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 · May 29, 2019

Chihiro said:
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...

Chihiro · May 29, 2019

... 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 · May 29, 2019

Chihiro said:
... 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?

Chihiro · May 29, 2019

... and the Return calculation...

Oscarr · May 29, 2019

Chihiro said:
... and the Return calculation...

OK solve

...Thanks bro

Oscarr · May 31, 2019

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.)..

Chihiro · May 31, 2019

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 · May 31, 2019

Chihiro said:
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 · May 31, 2019

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.
https://chandoo.org/forum/threads/useful-powerquery-tricks-chihiros-notes.35658/

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

Oscarr · Jun 3, 2019

Chihiro said:
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.
https://chandoo.org/forum/threads/useful-powerquery-tricks-chihiros-notes.35658/

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 · Feb 19, 2020

Chihiro said:
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.
https://chandoo.org/forum/threads/useful-powerquery-tricks-chihiros-notes.35658/

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 · Feb 19, 2020

Here's quick sample.

Oscarr · Feb 20, 2020

Chihiro said:
Here's quick sample.

hi @Chihiro

can this formula have date to choose?

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

Chihiro · Feb 20, 2020

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 · Feb 20, 2020

Chihiro said:
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 ...

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

Member

Attachments

Excel Ninja

Member

Excel Ninja

Attachments

Member

Excel Ninja

Member

Attachments

Excel Ninja

Member

Excel Ninja

Member

Attachments

Excel Ninja

Member

Attachments

Excel Ninja

Member

Member

Attachments

Excel Ninja

Member

Excel Ninja

Member

Member

Excel Ninja

Attachments

Member

Excel Ninja

Member