Cumulative Total not effected by slicer

[Drgodfrey]

Hi there,

I am trying analyse my inventory over time. I have imported the item journal table from Nav Dynamics and can successfully display the inventory over time using following measure:

Quantity running total in Posting_Date =

CALCULATE(

    SUM('Item Journal Card'[Quantity]),

    FILTER(

        ALL('Item Journal Card'[Posting_Date]),

        ISONORAFTER('Item Journal Card'[Posting_Date], MAX('Item Journal Card'[Posting_Date]), DESC)

    )

)

Producing the following graph:

The issue comes when I use a slicer to focus in on a particular time period, as the item journal card works by adding or subtracting a qty from the start point it resets the calculation for the start date on the slicer giving me the wrong calculations

I assume this is quite straight forward and I am missing something obvoius .... 

Thanks in adavnce 

D

[Nishantjain]

@Drgodfrey 

I am trying to understand the question and have mocked up the sample data. In  the image below, you can see there are 2 slicers, one for each table. I can see the the cumulative total is giving the same cumulative total in both table , irrecpective of the selected date rage.

Is this what you are looking for? or am I missing something. You can download the pbix file here

[v-easonf-msft]

Hi , @Drgodfrey 

I'm a little confused by your description. And your image is missing.

Sample data and expected output would help tremendously. 

Could you please share your PBIX/some sample data and expected result to me if you don't have any Confidential Information?
You can upload your pbix file to OneDrive and share it with me if it is convenient.

Best Regards,
Community Support Team _ Eason

[Drgodfrey]

Hi there

Sorry for the delay! 

The picture is below is the graph that gets produced by the data above. Essentailly it is an inventory movement table that i have exported from Nav Dymamics.  I can get an inventory on a set date by working a slicer back wards and the cumultaive calc stops at the slicer date. But if i narrow the date from the start date the movements prior are ignored and the invenotry is wrong. 

Essentilaly the output required is a table that has the inevntory on each day for each item.  

A link of sample data is below

https://1drv.ms/u/s!AoFC8Tq7aHhrtGwA_t8hpYC4SU-P?e=2QL3mJ

[v-easonf-msft]

Hi, @Drgodfrey 

Can you show me  the  formula  of Item Journal Card'[Quantity]  and Item Journal Card'[Posting_Date]   you create?

Best Regards,
Community Support Team _ Eason

[Drgodfrey]

Sorry I do not understand, those to are just columns in a table.

Essentially the current data relies on a + or - entry against a particular item code on a posting date.

A + item is receipt of stock

A - item is sale of stock

I am trying to focus my analysis into the last few months in order to help set my reorder points for inventory planning

At the moment if I slice the dates to focus on the last 3 months  then the calculation of + and - is only for that period, essentially it would be good to calculate an inventory table for each item on a particular date so i can interrogate my historic inventory 

Thanks for your continued help

Dave

[Drgodfrey]

Hi there

Sorry for the delay! 

The picture is below is the graph that gets produced by the data above. Essentailly it is an inventory movement table that i have exported from Nav Dymamics.  I can get an inventory on a set date by working a slicer back wards and the cumultaive calc stops at the slicer date. But if i narrow the date from the start date the movements prior are ignored and the invenotry is wrong. 

Essentilaly the output required is a table that has the inevntory on each day for each item.  

A link of sample data is below

https://1drv.ms/u/s!AoFC8Tq7aHhrtGwA_t8hpYC4SU-P?e=2QL3mJ

[Ashish_Mathur]

Hi,

Please show the exact result you are expecting in a simple Table format.  The visualisation can always be build after that.