Inventory Aging with As of Date

[abdulhadin]

Hello,

I hope someone can help me with this issue.

I am building an inventory aging report with the ability to specify an as of date. The way the report works, is I see the net available qty as of the date the user selects from the dropdown, and then I go back to find all the purchases that happened before the as of date, until I hit the net available qty. I find the qty that was purchased according to the aging buckets assigned. I am able to get all this to work for each line, but the total isn't showing correctly.

I have tried multiple formulas to resolve this without success so far. The last formula I used is this 

PurchaseAge01 - 00 to 30 Days =
var Days30 = min([AsOfDateQty], CALCULATE([AsOfDatePurchaseQty],Filter(MEINVENTAGING2, [TotalPurchaseDate] <= 30 )))
Var TotalDays30 = SUMMARIZE(MEINVENTAGING2, "30Days", Days30)

return
if(ISFILTERED(MEINVENTAGING2), Days30, SUMX(TotalDays30, [30days]))

Here is another formula that I am using

PurchaseAge02 - 31 to 60 Days = if(CALCULATE([AsOfDatePurchaseQty],Filter(MEINVENTAGING2,[TotalPurchaseDate]<=30)) >= [AsOfDateQty], 0,
min([AsOfDateQty] -
CALCULATE([AsOfDatePurchaseQty],Filter(MEINVENTAGING2,[TotalPurchaseDate]<=30)), CALCULATE([AsOfDatePurchaseQty],Filter(MEINVENTAGING2,[TotalPurchaseDate]<=60))-
CALCULATE([AsOfDatePurchaseQty],Filter(MEINVENTAGING2,[TotalPurchaseDate]<=30))))

Does anyone know how to resolve this?

Thanks

[amitchandak]

@abdulhadin ,

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

I think you need bucketing/binning on measure using an independent table.

Refer if this video of blog can help to get the approach

https://www.youtube.com/watch?v=CuczXPj0N-k

https://www.daxpatterns.com/dynamic-segmentation/
https://radacad.com/grouping-and-binning-step-towards-better-data-visualization

[abdulhadin]

Ok Here is a sample data, I am still not able to figure this out

Item Item group DatePhysical Qty Issue Receipt
123 Abc 2/3/2021 -4 1 0
123 Abc 1/26/2021 -6 1 0
123 Abc 1/26/2021 2 0 1
123 Abc 1/23/2021 -1 1 0
123 Abc 1/21/2021 -1 1 0
123 Abc 11/26/2020 12 0 1
123 Abc 11/25/2020 1 0 1
123 Abc 11/25/2020 -6 1 0
123 Abc 11/15/2020 6 0 1
456 Abc 1/18/2021 2 0 1
456 Abc 1/8/2021 -3 1 0
456 Abc 1/7/2021 3 1 0
456 Abc 6/17/2020 2 0 1
456 Abc 6/17/2020 -2 1 0
Sample data

Sample output:
In the date dropdown I am only showing End of month dates
If the date dropdown selected is 2/28/2021:
Item group Item Qty 0-30 30-60 60-90 90+
Abc 123 3 0 2 0 1
Abc 456 2 0 2 0 0
Total 5 0 4 0 1

If the date dropdown selected is 1/31/2021:
Item group Item Qty 0-30 30-60 60-90 90+
Abc 123 7 2 0 5 0
Abc 456 2 2 0 0 0
Total 9 4 0 5 0

If the date dropdown selected is 12/31/2020:
Item group Item Qty 0-30 30-60 60-90 90+
Abc 123 13 0 13 0 0
Total 13 0 13 0 0

I also have a separate calendar table.

Current measures:
As of date = var CurrentEndDate = max(MEINVENTAGING2[DATEPHYSICAL])
return
if(HASONEVALUE(DateCalendar[EOM]),VALUES(DateCalendar[eom]),CurrentEndDate)

TotalPurchaseDate = round([AsOfDate] - CALCULATE(MAX(MEINVENTAGING2[DATEPHYSICAL]), FILTER(MEINVENTAGING2, MEINVENTAGING2[STATUSRECEIPT] <> 0)),0)

AsOfDatePurchaseQty = CALCULATE(sum(MEINVENTAGING2[SUMOFQTY]),FILTER(MEINVENTAGING2, [TotalPurchaseDate] >= 0))

TotalDaysOpen = round([AsOfDate] - max(MEINVENTAGING2[DATEPHYSICAL]),0)
AsOfDateQty = CALCULATE(sum(MEINVENTAGING2[SUMOFQTY]),FILTER(MEINVENTAGING2,[TotalDaysOpen] <= 99999),FILTER(MEINVENTAGING2, [TotalDaysOpen] >= 0))

Now I am struggling with how to calculate the 0-30, 30-60 and so on. It is working fine for per item but at the total level, the total qty is correct, but the buckets aren't correct, it is grouping everything under the 0-30 and the rest of the buckets are empty.

Here is what I have for 0-30
PurchaseAge01 - 00 to 30 Days =
var Days30 = min([AsOfDateQty], CALCULATE([AsOfDatePurchaseQty],Filter(MEINVENTAGING2, [TotalPurchaseDate] <= 30 )))
Var TotalDays30 = SUMMARIZE(MEINVENTAGING2, "30Days", Days30)
return
if(ISFILTERED(MEINVENTAGING2), Days30, SUMX(TotalDays30, [30days]))

For Issue and receipt columns, the Issue is for when inventory is removed and Receipt is when inventory is received.

Thank you very much for your help and sorry for the long message.

[abdulhadin]

@amitchandak 

Do you think this is helpful? can you help me with what I am trying to do?

Thanks

[abdulhadin]

Thank you for your help, I will take a look at it, and see if I can use them in my solution, if not then I will create a sample pbix file. The issue is I don't alway have access to the data, so I will have to wait until tomorrow to try it out.

Thanks