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
)
