• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

  • sample 1.xlsx
    10.5 KB · Views: 7
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.

60329

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:
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?
 
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:
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

  • smp.PNG
    smp.PNG
    16.6 KB · Views: 10
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.
 
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

  • smp.PNG
    smp.PNG
    16 KB · Views: 3
... 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.
 
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

  • Error.PNG
    Error.PNG
    3 KB · Views: 3
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.
 
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
 
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.
 
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..
 
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
 
It's not formula really. Just transformation steps using some functions . You can easily adopt to any date ranges using filter on date colum.
 
Back
Top