Aging report with only limited data

[marieke]

Hi all,

Not sure to which category this issue belongs, perhaps it can be solved with DAX, or in Query Editor, or calculated columns… But hope somebody can help me out.

I have a dataset that contains a table called 'StockMovement History" that contains the number of products on stock for each day, and each product.

There is also a table with Sales Orders.

From this we would like to make an aging report, to see which part of the stock was purchased between 0-3 months ago, which between 3-6 months ago and so on.

So looking at product 25007, this stock arrived on 16-03-2021 and yesterday only on 20-04 only 609 were left. So if i set a date filter on today, I would like to see that 609 products were in aging categorie 0-3 months. But when i set a date filter on 16-03, this should be 2200 products, and when i set a date filter on 01-03, it should show that there was nothing in stock at that moment.

But I feel there is not enough data in this table to make this analysis. I’ve read a lot on this forum but can’t find anything simular to this. I think we should have the incoming date, or determine this from the qty when it goes up some day, but I dont know how to do this…

I have attached a dummy report with only 2 of the products in it, but there are a lot more products in the original dataset.

It would be great if somebody can point me in the right direction to get this sorted.

Many thanks in advance!

Aging Dummy Report.pbix (728.5 KB)

[jbressan]

Hi @marieke

Please check this post

Dynamic Grouping of Receivables

and this

Dynamic reporting of accounts receivables ageing

Hi I’ve been listening to one of the Webinar Series Resources on ageing of accounts receivable - here is the link, As the date slicer is moved around, the total value does not change dynamically for the reports on the page - (just after about 55 mins on the recording). I’ve tried to do a fix but unable to. Would appreciate if I could please get some help understanding what needs to be fixed to report the correct results as the date slicer is changed. Thanks Manoj

[marieke]

Hi @jbressan,

thanks for your reply. I did already watch the video from @sam.mckay prior to my post and indeed this is what I;m looking for, only instead of due dates I would like to see aging dates. On another project I made something simular like this with outstanding invoices.

But the challenge in this case is that I don’t know how to make my dataset work for this. As I don’t have a data column in it with the receive date for incoming goods. Perhaps that date can be distracted from the QTY column on each date (if QTY is 1000 on day 1 and 2000 on day 2, it means that new goods has arrived) but I really have no idea if that is possible to do and if so, how I should do that.

I’ve already tried a few things but nothing seems to be working for it

[EnterpriseDNA]

Hi @marieke! We noticed that your inquiry was left unsolved for quite some time now. We strive to answer ALL inquiries in the forum. However, if you are sure that you provided all pertinent context to your concerns and read how to use the forum more effectively and still find your question unanswered, you can check out tutorials to learn this yourself as your membership also comes with relevant resources that may help you with your Power BI education.

While our users and experts do as much as reasonable to help you with your inquiries, not all concerns can be attended to especially if there are some learnings to be done. Thank you!