DAX for Submission KPI

Question

Hi Everyone,&nbsp;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 are:1. for deliveries done on Monday to Wednesday - the invoice must be submitted on or before Friday (same week)&nbsp;2. for deliveries done on Thursday to Sunday - the invoice must be submitted on or before Tuesday (next week)&nbsp;Requesting help for correct DAX&nbsp;THANKS!&nbsp;

wilson_ · Answer

Hi Errol,&nbsp;Looks like you would benefit from learning about the WEEKDAY function&nbsp;and/or the SWITCH function. Hope that helps. Happy to help if you get stuck along the way.----------------------------------If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

tamerj1 · Answer

Hi&nbsp;koyDominic&nbsp;
please try
Invoice Submission KPI =VAR TimeOut = 'Table'[Delivery - Time Out]VAR MaxDate = TimeOut + 4VAR Dates =CALENDAR ( TimeOut, MaxDate )VAR MaxDate1 =MAXX ( FILTER ( Dates, WEEKDAY ( [Date], 2 ) = 5 ), [Date] )VAR MaxDate2 =MAXX ( FILTER ( Dates, WEEKDAY ( [Date], 2 ) = 2 ), [Date] )RETURNSWITCH (TRUE (),WEEKDAY ( TimeOut, 2 )IN { 1, 2, 3, 4 }&amp;&amp; TimeOut &lt;= MaxDate1, "Compliant",WEEKDAY ( TimeOut, 2 )IN { 5, 6, 7 }&amp;&amp; TimeOut &lt;= MaxDate2, "Compliant","None compliant")

wilson_ · Answer

Hello Errol,I was hoping you would be more specific on where you were actually getting stuck, for learning's sake. 🙂&nbsp;In any case, this was my DAX for your calculated column:Submission KPI = 
VAR DayOfTheWeek = WEEKDAY ( Table1[Delivery Time Out], 2 )
VAR InvoiceDeadline =
SWITCH (
    TRUE(),
    DayOfTheWeek &lt;= 3, Table1[Delivery Time Out] - DayOfTheWeek + 5, -- Time Out - DayoftheWeek will always return the prior Sunday; +5 gets to Friday
    Table1[Delivery Time Out] - DayOfTheWeek + 9 -- Time Out - DayoftheWeek will always return the prior Sunday; +9 gets to the following Tuesday
)

RETURN
Table1[Invoice Submitted] &lt;= InvoiceDeadline&nbsp;Created my own mini dataset for testing and this is what I get in my table:----------------------------------If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

koydominic · Answer

Hi&nbsp;Wilson,&nbsp;Thanks for the reply. I have tried the Weekday function yesterday but I cannot get the result.I will try the switch function.&nbsp;Thanks&nbsp;

koydominic · Answer

Hi&nbsp;tamerj1,&nbsp;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.&nbsp;&nbsp;&nbsp;

Forum Discussion

DAX for Submission KPI

7 Replies

Related content

Submission Compliance %

Online editing of semantic model measures submission failed

Dax

DAX

DAX Behavior