Calculating violation

[erhan_79]

Hi there ;

i need your kind support for below issue pls : 

i have table as below , the yellow marked last colum that i want to create in my table.I need dax formula for column , not measure.

So pls let me explain what is this table and in which rules i need yellow column.

in this table there are materials that indexed as group for each them , there are request quantities and request dates  , delivery quantities and  delivery dates ,

" open quantity" column shows : request quantity -delivery quantity

"statu"  column shows that  if all the materials delivered or not , if all material delivered closed , if not open.

i  would like to find if there is violation while delivering quantities.So rules like that : 

• IF the status is "open" system will check next all row's delivery quantity for each material , every checking will be for the material that we are on .  if there is even 1 pcs delivery quantity in next rows  system will write last column " violation" , if there is o ay delivered quantity in all next lines then here will be blank
• If the status is "closed" , system will check request delivery date ad will compare delivery date of the next line's.If in the one next lines there will be a delivery date earlier than its own  request date again system will write "violation" , if not will be blank
• For each material for the lastest row system will write blank that yellow column

to make your calculation easier i am sharig with you excels ource as below :

https://drive.google.com/file/d/1252yy235azu94tCWCUDAbjI3f1FPEqvy/view?usp=sharing

i hope it is clear thanks in advance for your kind help

[TomMartens · Accepted solution]

Hey @erhan_79 ,

I assume this DAX statement allows to create a calculated column that returns what you are looking for:

violation = 
var __Material = 'Table'[Material]
var __Index = 'Table'[Index]
var __Status =  'Table'[Status]
var __DeliveryDate = 'Table'[Delivery Date]
var doSucceedingDeliveriesExist =
    IF( __Status = "Open"
        , IF(
            CALCULATE( 
                MAX( 'Table'[Delivery Quantity] )
                , FILTER(
                    ALL( 'Table')
                    , 'Table'[Material] = __Material && 'Table'[Index] > __Index
                )
            ) > 0
            , "Violation"
            , BLANK()
        )
    )
var doOpenPreceedingDeliveriesExist =
    IF( __Status = "Closed"
        , IF(
            CALCULATE( 
                MIN( 'Table'[Delivery Date] )
                , FILTER(
                    ALL( 'Table')
                    , 'Table'[Material] = __Material && 'Table'[Index] > __Index
                )
            ) <= __DeliveryDate
            , "Violation"
            , BLANK()
        )
    )
return
IF( doSucceedingDeliveriesExist = "Violation" || doOpenPreceedingDeliveriesExist = "Violation"
    , "Violation"
    , BLANK()
)

Here is a screenshot of the result, be aware that I call the column violation:

Hopefully, this provides what you are looking for.

Regards,

Tom

[erhan_79]

This is exactly what i look for , thank you very much @TomMartens !