Find min date where continuous 8 rows of data in the calculated measure flag is 1

[kay87]

Hello everyone

I have created a measure called "CostFlag" which flags the rows where the "Output" is greater than the "TotalAvg". My queries are:

- How to flag continuous 8 rows where the CostFlag is 1

- Find min date of the CostFlag - marked in blue in the image below

Please find the screenshot below for your reference.

Thanks

Kay

[v-chuncz-msft]

@kay87,

You may take a look at this thread.

[kay87]

Thank you for the link. As I understand this is to add a new column to the data table but I am looking to create a measure to do the above. 

Please correct me if I am wrong as I just stared with Power BI and making a transtion from being an advanced user in Qlik to PBI. This could easily be achieved in Qlik by using rangemax function in the straight table.

Regards

Kay

[Zubair_Muhammad · Accepted solution]

Hi @kay87

Please give this a shot

Measure =
VAR mytable1 =
    FILTER ( VALUES ( Table1[Date] ), [CostFlag] = BLANK() )
VAR mytable2 =
    ADDCOLUMNS (
        mytable1,
        "Date Diff", DATEDIFF (
            [Date],
            MINX ( FILTER ( mytable1, [Date] > EARLIER ( Table1[Date] ) ), [Date] ),
            DAY
        )
            - 1
    )
RETURN
    MINX ( FILTER ( mytable2, [Date Diff] >= 8 ), [Date] )
        + 1

[richardnlove918]

Help! I'm getting the following error when I create the measure  "Column 'CostFlag' cannot be found or may not be used in this expression."