Automatically count the number of consecutive points decreasing / increasing

[LR]

Hi Team !

I would like to highlight areas on a graph where 6 consecutive points (or more) are increasing or decreasing.

How to perform such calculation ? 


Regards

[v-shex-msft]

HI @LR,

Current power bi not contains similar feature to achieve your requirement.

In my opinion, I'd like to suggest you write calculated column to check your records status to add category tag when they suitable for your requirement.

After these steps, use above column as chart legend and manually modify its data color of tagged category.

BTW, current power bi not support use multiple value field and legend field at same time.

Regards,

Xiaoxin Sheng

[LR]

Hi @v-shex-msft,

Thank you for your response !

I tried to create 2 new columns : one for consecutive increasing points and one for consecutive decreasing points.
I created a third column too. It serves to highlight areas on the graph :

     IF (
           OR (
                    [PointsIncreasing] >= 6 ;
                    [PointsDecreasing] >=6
           );
          'Table'[Value] ;
          Blank()
     ) 

I don't know yet how to counts the increasing/decrasing number of consecutive points.
Do you have any idea or research leads ?

Regards

[v-shex-msft]

Hi @LR,

Unfortunately, current charts not support use multiple values and legend at same time. I don't think you can use that method to achieve your requirement.

Can you please share some sample file to test and coding formula?

Regards,
Xiaoxin Sheng

[LR]

Hi @v-shex-msft,

You can find a sample file at this adress :  

https://drive.google.com/open?id=1DmEpQRlIp6SwSeJ1_2uwVrsy0mhTjMjSo52ABetGpCY

Thank you,
Regards

[v-shex-msft]

HI @LR,

As I said, you can simply use below column formula to mark tags of each records.

Category =
VAR previous =
    MAXX (
        FILTER (
            ALL ( Table1 ),
            [Type] = EARLIER ( [Type] )
                && [Date] < EARLIER ( [Date] )
        ),
        [Values]
    )
VAR result = [Values] - previous
RETURN
    IF (
        previous <> BLANK (),
        IF ( result > 0, "increasing", IF ( result < 0, "decreasing" ) )
    )

BTW, current power bi not support multiple legends or combine use legend fields and multiple value fields, so I think you can't use these columns to create graph in power bi .

Regards,
Xiaoxin Sheng

[LR]

Hi @v-shex-msft,

I am sorry for going out of time to answer.

Your formula tells me if a point is increasing or decreasing compare to the one before by type.

I noticed in the table below that some lines are not completed.
The fact that the first line is empty is normal in my opinion. But why are there others ?
I don't think that is due to equal values ​​because these lines are well completed in the column.
You can see it in the picture below, at the end of Type B.

    

Now, if we look at the graph, I put in color the parts said to be "decreasing".
We see that sometimes there are increasing points considered as decreasing (it is also visible in the table)...


To get the column "Count decreasing" in the table I went through an intermediate step.
I filled the clolonne of 1 when Category = "decreasing".
Only, for the final phase (ie. to count the number of consecutive points that are decreasing, respectively increasing) I have to formulate it in some way in my formula. Do you have ideas of how to do it?

Thank you very much for your time and your answers,

Kind Regards,
LR