Cumulative Total not effected by slicer

Question

Hi there,&nbsp;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:&nbsp;Quantity running total in Posting_Date = CALCULATE(&nbsp;&nbsp;&nbsp;&nbsp;SUM('Item Journal Card'[Quantity]),&nbsp;&nbsp;&nbsp;&nbsp;FILTER(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ALL('Item Journal Card'[Posting_Date]),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ISONORAFTER('Item Journal Card'[Posting_Date], MAX('Item Journal Card'[Posting_Date]), DESC)&nbsp;&nbsp;&nbsp;&nbsp;))&nbsp;Producing&nbsp;the following graph:&nbsp;&nbsp;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&nbsp;the start point it resets the calculation for the start date on the slicer giving me the wrong calculations&nbsp;I assume this is quite straight forward and I am missing something obvoius ....&nbsp;&nbsp;Thanks in adavnce&nbsp;D&nbsp;

v-easonf-msft · Answer

Hi ,&nbsp;Drgodfrey&nbsp;&nbsp;I'm a little confused by your description. And your image is missing.Sample data and expected output would help tremendously.&nbsp;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.&nbsp;Best Regards,Community Support Team _ Eason

drgodfrey · Answer

Hi there&nbsp;Sorry for the delay!&nbsp;&nbsp;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.&nbsp; 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.&nbsp;&nbsp;Essentilaly the output required is a table that has the inevntory on each day for each item.&nbsp;&nbsp;&nbsp;A link of sample data is below&nbsp;https://1drv.ms/u/s!AoFC8Tq7aHhrtGwA_t8hpYC4SU-P?e=2QL3mJ&nbsp;&nbsp;&nbsp;

drgodfrey · Answer

Hi there&nbsp;Sorry for the delay!&nbsp;&nbsp;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.&nbsp; 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.&nbsp;&nbsp;Essentilaly the output required is a table that has the inevntory on each day for each item.&nbsp;&nbsp;&nbsp;A link of sample data is below&nbsp;https://1drv.ms/u/s!AoFC8Tq7aHhrtGwA_t8hpYC4SU-P?e=2QL3mJ&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

v-easonf-msft · Answer

Hi,&nbsp;Drgodfrey&nbsp;Can you show me&nbsp;&nbsp;the&nbsp; formula&nbsp; of&nbsp;Item Journal Card'[Quantity]&nbsp; and Item Journal Card'[Posting_Date]&nbsp;&nbsp;&nbsp;you create?&nbsp;Best Regards,Community Support Team _ Eason

drgodfrey · Answer

Sorry I do not understand, those to are just columns&nbsp;in a table.&nbsp;Essentially the current data relies on a + or - entry against&nbsp;a particular item code on a posting date.&nbsp;A + item is receipt&nbsp;of stockA - item is sale of stock&nbsp;I am trying to focus my analysis into the last few months in order to help set my reorder points for inventory planning&nbsp;At the moment if I slice the dates to focus on the last 3 months&nbsp; then the calculation of + and - is only for that period, essentially&nbsp;it would be good to calculate an inventory table for each item on a particular date so i can interrogate&nbsp;my historic inventory&nbsp;&nbsp;Thanks for your continued&nbsp;help&nbsp;Dave

Forum Discussion

Cumulative Total not effected by slicer

7 Replies

Related content

Cumulative Total

Interesting effects with calendar-based time intelligence

Embed - Effective Identity

Creating Cumulative Graphs

Cumulative totals (Not effected by date slicer) beside totals that are effected by date slicer