Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
koyDominic
Regular Visitor

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 are:

1. for deliveries done on Monday to Wednesday - the invoice must be submitted on or before Friday (same week) 

2. for deliveries done on Thursday to Sunday - the invoice must be submitted on or before Tuesday (next week)

 

Requesting help for correct DAX

 

THANKS!

 

Submission KPI.JPG

7 REPLIES 7
SamInogic
Super User
Super User

Hi @koyDominic ,

You can achieve this KPI of 2 points mentioned with following below steps:


1. First Create a column “Same Week Friday” with below DAX expression to calculate Friday of Same Week:

Same Week Friday = 'Delivery Times'[Delivery Timeout].[Date] - WEEKDAY('Delivery Times'[Delivery Timeout].[Date],2) + 5

 

SameWeekFriday.png

2. Now Create Column “Next Week Tuesday Date” with DAX expression as below which gives us the Next Week Tuesday Date:

Next Week Tuesday = 'Delivery Times'[Delivery Timeout].[Date] - WEEKDAY('Delivery Times'[Delivery Timeout].[Date],2) + 9

Next Week Tuesday.png
3. Create a column to calculate KPI based on conditions you mentioned as below DAX expression:

KPI Value = IF(WEEKDAY('Delivery Times'[Delivery Timeout].[Date]) == 2 || WEEKDAY('Delivery Times'[Delivery Timeout].[Date]) == 3 || WEEKDAY('Delivery Times'[Delivery Timeout].[Date]) == 4,

IF('Delivery Times'[Invoice Submitted].[Date] > 'Delivery Times'[Delivery Timeout].[Date] && 'Delivery Times'[Invoice Submitted].[Date] <= 'Delivery Times'[Same Week Friday].[Date], "Compliant","Non-Compliant"),

IF('Delivery Times'[Invoice Submitted].[Date] > 'Delivery Times'[Delivery Timeout].[Date] && 'Delivery Times'[Invoice Submitted].[Date] <= 'Delivery Times'[Next Week Tuesday].[Date], "Compliant","Non-Compliant")

)

OutputLast.png

If this answer helps, please mark it as an Accepted Solution so it would help others to find the solution.

Thanks!

Inogic Professional Service Division

An expert technical extension for your techno-functional business needs

Power Platform/Dynamics 365 CRM

Drop an email at crm@inogic.com

Service:  http://www.inogic.com/services/ 

Power Platform/Dynamics 365 CRM Tips and Tricks:  http://www.inogic.com/blog/

Inogic Professional Services: Power Platform/Dynamics 365 CRM
An expert technical extension for your techno-functional business needs
Drop an email at crm@inogic.com
Service: https://www.inogic.com/services/
Tips and Tricks: https://www.inogic.com/blog/
tamerj1
Super User
Super User

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

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. 

 

KPI.JPG 

@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"
    )
koyDominic
Regular Visitor

Hi @Wilson,

 

Thanks for the reply. I have tried the Weekday function yesterday but I cannot get the result.

I will try the switch function.

 

Thanks 

Hello Errol,

I was hoping you would be more specific on where you were actually getting stuck, for learning's sake. 🙂

 

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 <= 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] <= InvoiceDeadline

 

Created my own mini dataset for testing and this is what I get in my table:

Wilson__0-1682275974670.png


----------------------------------
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?)

Wilson_
Memorable Member
Memorable Member

Hi Errol,

 

Looks like you would benefit from learning about the WEEKDAY function 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?)

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.