Forum Discussion

koyDominic's avatar
koyDominic
Regular Visitor
3 years ago

DAX for Submission KPI

Hi Everyone,   I need help fo a DAX for invoice submission KPI. I will create a new column to know if I achieved my KPI for invoice submission. My baseline is the delivery timeout column, my KPIs ...
koyDominic's avatar
koyDominic
Regular Visitor
3 years ago

Hi tamerj1,

 

Thank you and I appreciated your help. I tried the above, however all results shows "compliant". I guess the BI calculated based only on the weekdays but did not consider dates. I attached reference picture of the result below. The marked in red are supposed to be non compliant since it failed to submit the invoices before Tuesday (March 28) of the following week. The marked in yellow are correct. 

 

 

tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
3 years ago

koyDominic 
Oh! my mistake

Invoice Submission KPI =
VAR Submitted = 'Table'[Invoice Submitted]
VAR TimeOut = 'Table'[Delivery - Time Out]
VAR MaxDate = TimeOut + 4
VAR Dates =
    CALENDAR ( TimeOut, MaxDate )
VAR MaxDate1 =
    MAXX ( FILTER ( Dates, WEEKDAY ( [Date], 2 ) = 5 ), [Date] )
VAR MaxDate2 =
    MAXX ( FILTER ( Dates, WEEKDAY ( [Date], 2 ) = 2 ), [Date] )
RETURN
    SWITCH (
        TRUE (),
        WEEKDAY ( TimeOut, 2 )
            IN { 1, 2, 3, 4 }
            && Submitted <= MaxDate1, "Compliant",
        WEEKDAY ( TimeOut, 2 )
            IN { 5, 6, 7 }
            && Submitted <= MaxDate2, "Compliant",
        "None compliant"
    )