Running Sum: Use alternate date in report than date used to calculate running sum

[wi11iamr]

Hi there,

I have an InventoryForecast table for which I successfully calculate a Running Total/Cumulative measure by using the EndDate for each forecasted item. Code as follows:

Forecast Cost Cumulative:=
CALCULATE (
   SUMX('Inventory Planning', 'Inventory Planning'[Qty] * 'Inventory Planning'[Cost]),
    FILTER (
        ALL (' Inventory Planning'[EndDate] ),
        'Inventory Planning'[EndDate] <= MAX ('Inventory Planning'[EndDate] )
    )
)

I then have a DateKey ('dimCalendar') for which my 'Inventory Planning' table has a relationship for EndDate to the  dimCalendar[Date].

While I understand that the cumulative measure uses EndDate from it's same table to perform the running sum, if I wish to enrich my report by instead using columns from my dimCalendar (such as Month Name, Fiscal periods etc), the measure does not produce a running total and merely reflects the individual value for that month, as per the below screenshot.

Also, with an additional date field in my report, end users will continue to be fconfused and will very likely not use the "corect" EndDate field and when using the DateKey fields will end up with wrong results.

The question then is whether there is any means by which I can effectively hide the EndDate from the users so they select only from the DateKey fields, while still preserving the inegrity of my runnign sum calculation?

[Ashish_Mathur · Accepted solution]

Hi,

Drag the Date from the Calendar Table and write the following measure:

=CALCULATE(SUMX('Inventory Planning','Inventory Planning'[Qty]*'Inventory Planning'[Cost]),DATESBETWEEN('dimCalendar'[Date],MINX(ALL('dimCalendar'),'dimCalendar'[Date]),MIN('dimCalendar'[Date])))

Hope this helps.

[wi11iamr]

Thanks @Ashish_Mathur, this does in deed work well and also retains the relatiosnship with my Vendor table for further slicing.

[Ashish_Mathur]

You are welcome.

[v-cherch-msft]

Hi @wi11iamr

You may try below measure:

Forecast Cost Cumulative:=
CALCULATE (
   SUMX('Inventory Planning', 'Inventory Planning'[Qty] * 'Inventory Planning'[Cost]),
    FILTER (
        ALL (' Inventory Planning' ),
        'Inventory Planning'[EndDate] <= MAX ('Inventory Planning'[EndDate] )
    )
)

Regards,

Cherie

[wi11iamr]

Ah, beautiful, thanks @v-cherch-msft! While I see what you've done by removing the [EndDate] from the filter context, I admittedly don't fully comprehend the logic in doing so. If you have a minute, would you mind clarifying so I may better understand for future code?

That said, while the Running Sum now works well at an aggregated level, when I slice by another dimension, such as vendor. then each vendor has the exact same running total sum as the overall total. Any thoughts on how to remedy this?