• 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 meke 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

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

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

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:

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

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

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

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