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 ...
tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
3 years ago

Hi koyDominic 

please try

Invoice Submission KPI =
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 }
&& TimeOut <= MaxDate1, "Compliant",
WEEKDAY ( TimeOut, 2 )
IN { 5, 6, 7 }
&& TimeOut <= MaxDate2, "Compliant",
"None compliant"
)

  • 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"
    )