PowerBI - Rolling Monthly OTD Calculated Column

Question

I am pretty new to PowerBI and I am trying to calculate a rolling "Monthly On-Time Delivery" calculated column.

ERROR IMAGE

ADDED DAILY SHIPMENT COUNT (Disregard the incorrect MTD Shipment Count title)

Each row represents a single day in the Matrix

For each day I want to get the MonthShippedTotal / (MonthShippedTotal-MonthPastDue), this should give me my rolling on time delivery percentage.

This is what I have so far but the results are way off:

> MonthlyOTD =  VAR RowDate = opr_shipments[SHIP_DATE] RETURN
>     CALCULATE (
>         DIVIDE((COUNT(opr_shipments[CREATE_TIMESTAMP])-COUNTAX(opr_shipments,opr_shipments[PAST_DUE]="1")),COUNT(opr_shipments[CREATE_TIMESTAMP]),0),
>         FILTER (
>             opr_shipments,
>             opr_shipments[SHIP_DATE] <= RowDate
>                 && MONTH ( opr_shipments[SHIP_DATE] ) = MONTH ( RowDate ) && YEAR ( opr_shipments[SHIP_DATE] ) = YEAR ( RowDate )
>         )
>     )

MTD Shipments Calc:

MTD Shipments = 
VAR RowDate = opr_shipments[SHIP_DATE]
RETURN
    CALCULATE (
        SUM ( opr_shipments[Daily Shipments] ),
        FILTER (
            opr_shipments,
            opr_shipments[SHIP_DATE] <= RowDate
                && MONTH ( opr_shipments[SHIP_DATE] ) = MONTH ( RowDate ) && YEAR ( opr_shipments[SHIP_DATE] ) = YEAR ( RowDate )
        )
    )

Past Due Total Calc:

PastDueTotal = 
VAR RowDate = opr_shipments[SHIP_DATE]
RETURN
    CALCULATE (
        SUM(opr_shipments[Daily Shipments]),
        FILTER (
            opr_shipments,
            opr_shipments[SHIP_DATE] <= RowDate
                && DAY ( opr_shipments[SHIP_DATE] ) = DAY ( RowDate ) && opr_shipments[PAST_DUE] = "1"
        )
    )

For each month this calc would start over.

My desired output is: Jan 1 | 98% Jan 2 | 98.3% Jan 3 | 95% ... Jan 31 | 94.5% Feb 1 | 100%

What is the structure of your tables? how are being calculated MTD Shipments and Past Due Total measures? — alejandro zuleta
Is the Past Due Total MTD too? note you are specifying an additional condition in the filter PAST_DUE = 1 — alejandro zuleta
The Past Due Total is a daily count or total of the number of line items that were past due (i.e. WHERE PAST_DUE=1) — xXPhenom22Xx
So for Jan 15 for example I want to count all of the past due items from Jan1-Jan15 and then get the on time % — xXPhenom22Xx

alejandro zuleta alejandro zuleta · Accepted Answer · 2017-02-09T22:52:35

I think something like this could work for the MonthlyOTD calculated column.

MonthlyOTD =
VAR RowDate = opr_shipments[SHIP_DATE]
RETURN
    SUMX (
        FILTER (
            opr_shipments,
            [SHIP_DATE] <= RowDate
                && MONTH ( [SHIP_DATE] ) = MONTH ( RowDate )
                && YEAR ( opr_shipments[SHIP_DATE] ) = YEAR ( RowDate )
        ),
        DIVIDE ( [MTD Shipments], [MTD Shipments] - [PastDueTotal] )
    )

I'd create those calculations as measures instead of calculated columns, note measures can be calculated in any context and are affected by filters which usually is desired.

UPDATE: It seems OP doesn't require a monthly cumulative total so the final expression was:

MonthlyOTD = DIVIDE ( ([MTD Shipment Count] - [Lines]), [MTD Shipment Count] )

PowerBI - Rolling Monthly OTD Calculated Column

1 Answers