How to flag out whether the record exists in previous period

[Georgia_H]

Hello, i need some help. I want to achieve 2 things:

1. A calc column/measure to flag out new QIDs as 1 when compare against previous period (in my case previous week) for different Business Unit. 

2.  A calc column/measure to flag out existing QIDs as 0 when compare against previous period (in my case previous week) for different Business Unit. 

So far, I cannot get the results i wanted. See snipet in the highlighted blue box.

E.g: QID 38169 is a new QID detected in Week 25 for this particular LBU but is flagged out as 0 instead of 1. I also attached my formula. Please enlighten me what is amiss here. Thank you.

[Ashish_Mathur]

Hi,

Could you share the download link of your PBI file.

[v-eqin-msft · Accepted solution]

Hi @Georgia_H ,

According to my understanding, you want to set a flag based on QID that it will identify whether the QID is a new one or an existed one.

If so, please use the following formula to create a measure:

Flag =
VAR _t =
    SUMMARIZE (
        FILTER (
            ALL ( 'Table' ),
            'Table'[LBU] = MAX ( 'Table'[LBU] )
                && 'Table'[Last Detected] < MAX ( 'Table'[Last Detected] )
        ),
        [QID]
    )
VAR _first =
    CALCULATE (
        FIRSTNONBLANK ( 'Table'[Last Detected], TRUE () ),
        ALLEXCEPT ( 'Table', 'Table'[LBU] )
    )
RETURN
    IF (
        MAX ( 'Table'[Last Detected] ) = _first,
        0,
        IF ( MAX ( 'Table'[QID] ) IN _t, 0, 1 )
    )

The final output is shown below:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[vanessafvg]

it looks like your lastdetected column is a datetime?  if you comparing dates they both have to be dates ad not datetime.  

have you gone through your code variables and checked that each variable or condition you use is returning the values back you expect?

Also in terms  of your or statement, have you tested the values being returned?