Running total of full last 12 calendar months

[Hyperchef1969]

Hi,

I want to calculate the running total of the full last 12 calender months. This for Late, on time and grand total.

Above the pivottable of the dataset within excel.

So for 2018-02 on time = 1509. For 2018-03 this should be 1509+1922 etc.

Next month, so when we are in March 2019, calculation should be based on months 2018-03 till 2019-02. I am working with a datetable so when I am able to calculate the date range this should be incorporated within the calculate count function I assume.

Thanks for help in advance!

[v-cherch-msft]

Hi @Hyperchef1969 

You may try to create a measure like below:

Measure =
CALCULATE (
    SUM ( Table[On time] ),
    DATESINPERIOD ( 'Calendar'[Date], LASTDATE ( 'Calendar'[Date] ), -12, MONTH )
)

Regards,

Cherie

[Anonymous]

I think you should use earlier function like that;

column = CALCULATE(SUM(table[on_time]);FILTER(table;table[On_time_delivery_month]<=EARLIER(table;table[On_time_delivery_month])))

If you share your dataset as text i would help you better.

[Hyperchef1969]

Hi,

I implemented the running total of the total inbound shipments succesfully as by following calculation, but now I need to build extra filters for calculating the number of Early, Late, On time & Early+On time. These subcategories come from the field 'Purchasing_data[OTD 2d]. So the question is how to implement these extra filters in to the calculation I already have, so that I can create 4 extra measurements for running total in time, running total late, running total early and running total in time+early. Thanks for your help in advance.

Running_total_inbound = CALCULATE(COUNT(Purchasing_data[OTD 2d]),FILTER(ALLSELECTED(Date_table),Date_table[Date] <= MAX(Date_table[Date])))

[Hyperchef1969 · Accepted solution]

Solved myself with following calculation (can be accepted as solution 🙂 )

Running_total_on_time_and_early = CALCULATE(COUNT(Purchasing_data[OTD 2d]),Purchasing_data[OTD 2d]="on time" || Purchasing_data[OTD 2d]="early",FILTER(ALLSELECTED(Date_table),Date_table[Date] <= MAX(Date_table[Date])))

[v-cherch-msft]

Hi @Hyperchef1969 

Got it, if you have resolved the issue, please mark your reply as answer to close this thread.

Regards,

Cherie