Running total within time periods

[MMJ369]

Hi,

I have to create a graph that shows the value of orders placed every day within a timewindow which need to be shipped within that same time window (between the cutoff dates of two consecutive months). We need to see the ordervalue growing during this timeperiode.

I have it working if the time window is a month. But the cut off dates for ordering and shipping are within the month, mostly between the 15th and 20st of the month.

So the graph for when the period would be a calendarmonth is this.

Each period/month is a line and the X-axis is the day of the month.

The lines show the cumulative value of placed orders that can be shipped before the end of that month.

I now need to have the cumulative value of orders placed within the preiod and which can be shipped in that period.

OB[OrderDate]>OB[PreviousCutOffDate] && OB[OrderDate]<=OB[CutOffDate]

&& OB[ShipDate]<=OB[CutOffDate]

The cutoffdates are lookedup in a different table and stored as a calculated column against each line in OB table.

For the end of month calculation I use two measures:

PlacedOrdersThisMonth =
CALCULATE (
 SUM ( OB[OrderValue] ),
 USERELATIONSHIP ( OB[OrderDate], 'Calendar'[Date]),
     FILTER(OB, OB[ShipDate] <= EOMONTH(OB[OrderDate],0)
))

AND

MTDPlacedOrdersThisMonth =
CALCULATE (
    OB[PlacedOrdersThisMonth],
    FILTER (
        ALLSELECTED ( 'Calendar'[Date] ),
        ISONORAFTER ( 'Calendar'[Date], MAX ( 'Calendar'[Date] ), DESC )))

How can I get a similar graph for within the cutoffdate periods?

PBIX: test COTD.pbix

[MMJ369]

I now have the calculation and graph to show only orders which can be shipped before cutoff date of that order by amending the measure:

PlacedOrdersThisMonth =
CALCULATE (
 SUM ( OB[OrderValue] ),
 USERELATIONSHIP ( OB[OrderDate], 'Calendar'[Date]),
     FILTER(OB, OB[ShipDate] <= OB[CutOffDate]  )
)

This gives the graph:

where the red orders are not shown as these are shipped after cutoff.

The issue which I have left then is that the cumulative value is calculated for all orders, where I would like to start from zero value after each cutoff date. How could I achieve that?

[v-jiascu-msft · Accepted solution]

Hi @MMJ369,

Is it this one? Please check out the demo in the attachment.

PlacedOrdersThisMonth 3 =
CALCULATE (
    IF (
        MIN ( 'Calendar'[Date] ) = MIN ( OB[CutOffDate] ),
        0,
        SUM ( OB[OrderValue] )
    ),
    USERELATIONSHIP ( OB[OrderDate], 'Calendar'[Date] ),
    FILTER ( OB, OB[ShipDate] <= OB[CutOffDate] )
)

Best Regards,

Dale