Mapping for SO date and Invoice date

[eunji888888]

I have tried to create below measure to generate claim number, but i found that it is little bit difference with the correct number. After some investigation, it should be due to the OOS list. I have 3 scenarios: if netcase>=2, then give scenario1, if netcase=1 and there is an OOS item in the recently 7 days for the invoice date, then return scenario1, if netcase=1 and no oos item, then return 0. The problem is that for those OOS item, i will have a separate table, and need to find those oos item and then determine whether the "By SO" will need to count as which scenarios.

claim2 =

VAR _date = SELECTEDVALUE('by SO'[InvoiceDate]) - 7

VAR netCaseSum =

    SUMX(

        'by SO',  

        'by SO'[*NetCase]

    )

VAR isInOOSList =

    CALCULATE(

        COUNTROWS('2024 OOS'),

        FILTER(

            '2024 OOS',

            '2024 OOS'[SODate] > _date

            && '2024 OOS'[SODate] < SELECTEDVALUE('by SO'[InvoiceDate])

            && MONTH('2024 OOS'[SODate]) IN { 3, 4 }

            && '2024 OOS'[OOS Cu] > 0

        )

    )

VAR scenario1 =

    SUMX (

        FILTER (

            'by SO',

            LEFT ( 'by SO'[Reason Description], 2 ) <> "Gx"

        ),

        'by SO'[*NetCase] * 'by SO'[number2]

    )

RETURN

    SWITCH (

        TRUE (),

        netCaseSum >= 2, scenario1,

        netCaseSum = 1 && isInOOSList > 0, scenario1,

        netCaseSum = 1 && isInOOSList = 0, 0,

        0

    )

[v-yiruan-msft]

Hi @eunji888888 ,

Base on your description, it seems like you are trying to create a measure to get a claim number. However, it is not correct. Could you please provide some raw data in the table 'by SO' and '2024 OOS' (exclude sensitive data) with Text format and your expected result with backend logic base on your shared sample data? It would be helpful to find out the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards