Forum Discussion

erhan_79's avatar
erhan_79
Post Prodigy
5 years ago
Solved

Calculating violation

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

Need Help
  • TomMartens's avatar
    TomMartens
    5 years ago

    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

     

2 Replies

  • TomMartens's avatar
    TomMartens
    Super User
    5 years ago

    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