Forum Discussion

MMJ369's avatar
MMJ369
Frequent Visitor
8 years ago
Solved

Running total within time periods

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...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

    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] )
)

    Running_total_within_time_periods

     

    Best Regards,

    Dale

MMJ369's avatar
MMJ369
Frequent Visitor
8 years ago

 

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's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    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] )
)

    Running_total_within_time_periods

     

    Best Regards,

    Dale