Build a Measure for Past Due Purchase Order Amounts

[data_guy_87]

Hello, I am trying to total our historical past due balances on all PO lines. Here is a screenshot example of how we do it in excel currently: 

I have a PO_Line historical database table that shows changes in line statuses from released to received to closed status, along with planned vs wanted dates (if planned > wanted = past due). Here is an example of the data:

Is a measure the best route for this? Maybe create a calculated table? Any help is greatly appreciated!

[Anonymous · Accepted solution]

Hi @data_guy_87 
Thank you for reaching out microsoft fabric community forum. Also thank you @DataNinja777  for your inputs.

Since your PO line data is a historical fact table with [Valid From] and [Valid To] columns, the measure needs to ensure that it only considers PO lines valid for the specific snapshot date you're analyzing.

You can modify the DAX measure to filter PO lines where the snapshot date falls within the validity period. Here's a refined version of the Past Due PO Amount measure that incorporates the validity date range:

PastDuePOAmount =  
VAR SelectedDate = MAX('SnapshotDates'[Date])  
RETURN  
CALCULATE(  
    SUMX(  
        FILTER(  
            'PO_Lines',  
            'PO_Lines'[POL_WANTED_DEL_DATE] < SelectedDate &&  
            'PO_Lines'[PURCHASE_QTY] > 'PO_Lines'[QTY_ARRIVED] &&  
            'PO_Lines'[Valid From] <= SelectedDate &&  
            'PO_Lines'[Valid To] > SelectedDate  
        ),  
        'PO_Lines'[Unit_Price] * 'PO_Lines'[PURCHASE_QTY]  
    )  
)  

• The filter checks that the snapshot date (SelectedDate) is between [Valid From] and [Valid To].
• It also checks that the POL_WANTED_DEL_DATE is before the snapshot date, indicating past due.
• It confirms there is still undelivered quantity (PURCHASE_QTY > QTY_ARRIVED).
• Replace 'PO_Lines'[Unit_Price] * 'PO_Lines'[PURCHASE_QTY] with the appropriate line value calculation if your data stores amounts differently.

This measure will sum the past due amounts dynamically for each snapshot date while respecting the validity periods in your historical table.
If this solution helps, please consider giving us Kudos and accepting it as the solution so that it may assist other members in the community
Thank you.

[DataNinja777]

Hi @data_guy_87 ,

To calculate the Past Due Purchase Order Amounts in Power BI using DAX, a measure is the most appropriate method if you're dynamically analyzing past due status based on a snapshot date (e.g., weekly periods). From the data shown, each PO line includes fields like POL_WANTED_DEL_DATE, QTY_ARRIVED, and PURCHASE_QTY, which allows us to determine whether the delivery was overdue. A purchase order line is considered past due if the POL_WANTED_DEL_DATE is earlier than the snapshot date and if the quantity arrived is less than the quantity ordered.

If your report includes a table of weekly dates (snapshot points), you can create a custom date table to serve as the reference for each week's ending date.

SnapshotDates = 
ADDCOLUMNS(
    CALENDAR(DATE(2024,12,30), DATE(2025,12,31)),
    "Week Ending", [Date] + 6 - WEEKDAY([Date], 2)
)

Once you have this date context, you can write a DAX measure to calculate the past due PO amount by checking if the wanted delivery date is earlier than the selected snapshot date and whether the items have not been fully received. If you have a column for unit price or total value per line, include that in the calculation. Otherwise, you can use a simple count as a proxy.

PastDuePOAmount = 
VAR SelectedDate = MAX('SnapshotDates'[Date])
RETURN
CALCULATE(
    SUMX(
        'PO_Lines',
        IF(
            'PO_Lines'[POL_WANTED_DEL_DATE] < SelectedDate &&
            'PO_Lines'[PURCHASE_QTY] > 'PO_Lines'[QTY_ARRIVED],
            1 * 'PO_Lines'[Unit_Price],
            BLANK()
        )
    )
)

If you don't have pricing data, you can use a count of the PO lines that are past due.

PastDuePOCount = 
VAR SelectedDate = MAX('SnapshotDates'[Date])
RETURN
CALCULATE(
    COUNTROWS(
        FILTER(
            'PO_Lines',
            'PO_Lines'[POL_WANTED_DEL_DATE] < SelectedDate &&
            'PO_Lines'[PURCHASE_QTY] > 'PO_Lines'[QTY_ARRIVED]
        )
    )
)

This approach assumes your PO line data reflects the historical state or is tied to snapshot dates using a validity period. If your table is a historical fact table with valid-from and valid-to columns, you may need to adjust the logic to filter the PO lines that were valid as of the snapshot date. Let me know if that's the case, and I can help you refine the formula accordingly.

Best regards,

[data_guy_87]

Hello @DataNinja777 

Thank you for the help with this. To respond to the last part of your post, yes, I do have a historical fact table with [Valid From] and [Valid To] columns. Any additional help with the refined measure is greatly appreciated.