Count the most recent and consecutive values below 1

[Anonymous]

Hi all,

I'm struggling to find a working solution in powerbi for the following. I've got a dataset that's look like the one in the table below. 
I want to make a measure that counts how many times a product scores below 1,00 consecutively from the most recent available week selected.

For example: If I select all three weeks (column: Week nr), Milk should get a Desired value of 2, because it has been below 1,00 in the 2 most recent weeks.

Product	Week nr	Value	Desired value
Milk	2022_WK 52	1,00	0
Milk	2023_WK 01	0,90	1
Milk	2023_WK 02	0,95	2
Bread	2022_WK 52	0,85	1
Bread	2023_WK 01	0,90	2
Bread	2023_WK 02	1,00	0
Juice	2022_WK 52	1,00	0
Juice	2023_WK 01	0,90	1
Juice	2023_WK 02	1,00	0

Would anyone have an idea on how to tackle this problem? 
Any advice would be appreciated! Thanks for taking the time to read this 🙂

[Jihwan_Kim]

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

Desired result measure: =
VAR _newtable =
    ADDCOLUMNS (
        FILTER ( ALL ( Data ), Data[Product] = MAX ( Data[Product] ) ),
        "@index", IF ( Data[Value] >= 1, 1, 0 )
    )
VAR _groupingtable =
    ADDCOLUMNS (
        _newtable,
        "@group",
            SUMX (
                FILTER ( _newtable, Data[Week nr] <= EARLIER ( Data[Week nr] ) ),
                [@index]
            )
    )
RETURN
    IF (
        HASONEVALUE ( Data[Product] ),
        IF (
            MAX ( Data[Value] ) >= 1,
            0,
            COUNTROWS (
                FILTER (
                    _groupingtable,
                    [@index] <> 1
                        && Data[Week nr] <= MAX ( Data[Week nr] )
                )
            )
        )
    )

[Anonymous]

Hi Jihwan_Kim,

Thank you for taking the time to reply! You understood the question perfectly, but im afraid it did not work.

I want to share a sample of my actual dataset so you can see the result, but the forum wont allow me to upload the pbix file. How did you do this? 

[Jihwan_Kim]

Hi,

Thank you for your message, and you can use Onedrive, googledrive, or Dropbox (or any other method) to share the link of the pbix file.
Thanks.

[Anonymous]

Hi,

thanks for the tip. Im probably to new of a user to upload directly here.

I have created a dropbox link.
https://www.dropbox.com/t/d5kxUtQwA9IBXPDp

So basically, the data is the same, but expanded with multiple week numbers, and one extra variable (concurrent, which means competitor).

I didnt think both of these would interfere with your written solution, but apparantly it does. sorry for that