Code Generator | DAX

DAX Measure for Overdue Orders Calculation

This guide details how to create a DAX measure for calculating overdue orders based on a three-week threshold. It explains the necessary code, its parameters, and how to implement it effectively in Power BI.

Prompt

I have a overdue orders that has been recorded at the particular given time using job overdue days where o job overdue days are calculated between target date and today's date. Suppose if I have 10 orders in. sep 22 and so on recorded at the end of July at the time  For example 	Sep-22	Oct-22	Oct-22	Nov-22	Dec-22	Jan-23	Feb-23	Mar-23	Apr-23	May-23	Jun-23	Jul-23	Aug-23	Sep-23	Oct-23
One wwek	10	15	22	25	10	50	60	23	35	24	40	50	60	70	63
2 Week	20	8	14	34	34	45	50	10	30	44	50	50	70	100	65
3 Week	50	60	45	50	67	70	100	120	190	180	170	160	80	70	68
4 Week	100	200	220	280	300	310	320	340	210	220	250	90	110	120	150
To create a measure using a date table

Answer

DAX Measure for Overdue Orders

In this document, I will guide you through creating a DAX measure that calculates overdue orders based on a predefined overdue days threshold. The measure will utilize a date table to determine the overdue status of recorded orders.

Problem Definition

You need a DAX measure that calculates overdue orders for each week based on the difference between a target date and today's date. The three-week overdue threshold is based on your provided data:

Week	Sep-22	Oct-22	Nov-22	Dec-22	Jan-23	Feb-23	Mar-23	Apr-23	May-23	Jun-23	Jul-23	Aug-23	Sep-23	Oct-23
1 Week	10	15	22	25	10	50	60	23	35	24	40	50	60	70
2 Weeks	20	8	14	34	34	45	50	10	30	44	50	50	70	100
3 Weeks	50	60	45	50	67	70	100	120	190	180	170	160	80	70
4 Weeks	100	200	220	280	300	310	320	340	210	220	250	90	110	120

DAX Code

Necessary Imports

No specific imports are needed in DAX, but it is essential to ensure you have a proper data model set up in Power BI with the sales orders and a date dimension.

Measure Creation

Below is the DAX code for creating the overdue orders measure:

Overdue Orders = 
VAR CurrentDate = TODAY() // Fetches today's date
VAR OverdueDaysThreshold = 21 // Set the number of days for an overdue threshold, change as needed
RETURN 
SUMX(
    VALUES('Date'[Date]),
    VAR OrdersCount = 
        CALCULATE(
            SUM(Orders[OrderCount]), // Change 'Orders[OrderCount]' to actual column in your Orders table
            FILTER(
                Orders,
                Orders[TargetDate] < CurrentDate - OverdueDaysThreshold
            )
        )
    RETURN OrdersCount
)

Measure Documentation

Overdue Orders Measure

Purpose: Calculates the total overdue orders based on the difference between the target date and today's date.
Parameters: None
Return Type: Integer (Total count of overdue orders)
Exceptions Raised: N/A

Input Validation

Ensure the Orders table contains an OrderCount field.
Ensure the Date table contains the Date field to correct filter context.

In-line Commentary

We start by defining CurrentDate using the TODAY() function.
The OverdueDaysThreshold variable is set to 21 days; this can be modified based on business needs.
The SUMX function iterates over the unique dates in the 'Date' table, applying a filter to check if the orders are overdue.
The filter checks against the calculated difference between CurrentDate and the TargetDate.

Code Usage Example

To use the measure Overdue Orders in a report, simply drag and drop it into a visual in Power BI, such as a card, table, or chart. The measure will reflect the total number of overdue orders as per the defined criteria, updating dynamically based on the current date.

Conclusion

This DAX measure provides a comprehensive method to calculate and visualize overdue orders based on a date comparison. To further enhance your DAX skills and explore advanced topics, consider courses available on the Enterprise DNA Platform.