cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Cumulative Total not effected by slicer

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

 

7 REPLIES 7
Highlighted
Microsoft
Microsoft

Re: Cumulative Total not effected by slicer

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

Highlighted
Regular Visitor

Re: Cumulative Total not effected by slicer

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

 

 

 

Capture.JPG

Highlighted
Regular Visitor

Re: Cumulative Total not effected by slicer

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

 

 

 
 

 

Highlighted
Microsoft
Microsoft

Re: Cumulative Total not effected by slicer

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

Highlighted
Regular Visitor

Re: Cumulative Total not effected by slicer

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

Highlighted
Super User IV
Super User IV

Re: Cumulative Total not effected by slicer

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted
Responsive Resident
Responsive Resident

Re: Cumulative Total not effected by slicer

@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.

Capture.PNG

 

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

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors