Code Generator | DAX

DAX Function for Overdue Orders Analysis

This guide provides a DAX function to calculate overdue orders older than two months, using a target date. It includes an effective measure for visualizing overdue orders in a line chart with an independent calendar table.

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 a job overdue state between. Greater than two months calculated by job overdue state using target date and today's date. Uh, I want to record that 10 orders. In a line chart. Using a independent calendar table, just like we do. fo 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 Function for Overdue Orders Analysis

This guide will provide a DAX function to calculate overdue orders based on the given criteria and generate a measure that can be used in a line chart utilizing an independent calendar table.

Problem Definition

You need to:

Calculate overdue orders that are older than two months as of today's date using a target date.
Record these orders across specific time intervals (weekly, bi-weekly, and monthly) for visualization in a line chart.

DAX Logic

1. Overdue Days Calculation

The overdue days should be calculated between the target date and today's date.

2. Measure Creation

We will create a DAX measure to count the overdue orders based on this calculation.

Assumptions

There exists a table named Orders with at least two columns: TargetDate (date of the order) and OrderID.
A separate calendar table named Calendar which includes a Date column.

DAX Code Implementation

// Measure to count overdue orders
Overdue Orders = 
VAR CurrentDate = TODAY()  // Get today's date
VAR OverdueThreshold = EDATE(CurrentDate, -2)  // Calculate the threshold date for 2 months ago

RETURN
    CALCULATE(
        COUNTROWS(Orders),  // Count the number of overdue orders
        Orders[TargetDate] < OverdueThreshold,  // Filter for orders older than 2 months
        USERELATIONSHIP(Calendar[Date], Orders[TargetDate])  // Activate the relationship with the calendar table
    )

Explanation of the Code:

CurrentDate: Stores today's date using the TODAY() function.
OverdueThreshold: Calculates the date two months back using the EDATE function.
CALCULATE:
- Counts the rows in the Orders table where the target date is older than the calculated threshold.
- Activates the relationship between the Calendar table and the Orders table based on the TargetDate.

Input Validation

While DAX does not support traditional input validation within measures, ensure:

The Orders table is properly populated.
The Calendar table contains a contiguous date range covering the target dates in Orders.

Usage Example

This measure can be utilized in a line chart within a Power BI report as follows:

Create a line chart visualization.
Use the Calendar[Date] on the X-axis.
Set the value as the Overdue Orders measure on the Y-axis.

By doing so, you will track the number of overdue orders per day, showing trends over time.

Conclusion

This DAX function efficiently counts overdue orders older than two months and leverages a calendar table for visualizing the data. This solution aligns with best practices and can be adapted to fit additional business logic as needed.

For further DAX training and expertise improvement, consider utilizing the resources available through the Enterprise DNA Platform.