Measure with a loop to calculate future product stock

[pwrch]

Hi,

I have a table of items and the date they'll be in stock and I have another table that lists how many of each item we'll be getting over the next year or so. For example:

Table A:

Items	Date
A	7/1/2022
B	8/1/2022

Table B:

Date	Quantity
7/1/2022	50
8/1/2022	100
9/1/2022	50
10/1/2022	75

etc.

What I want to do is create a measure that looks up when an item will be in stock (from table A) and then loops month by month after that date using the quanity from Table B to return a date when I will have X amount of product available.

Thank you,

[sturlaws]

Hi,

not quite sure what you are after, but assuming you want to sum the values Table B for all months after the date in Table A, you can use this approach: pbix

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

How to Get Your Question Answered Quickly

[pwrch]

Kindoff yes. I want to add up the values thaat come after but only until they certain value (like for example 1000) and then return the date when that happens.

[Anonymous · Accepted solution]

Hi @pwrch ,

Here I create a sample to have a test.

Table A:

Table B:

Use What-if parameter to create a slicer. We can select the target number in it.

Measure:

Running Total = 
CALCULATE (
    SUM ( 'Table B'[Quantity] ),
    FILTER (
        ALL ( 'Table B' ),
        'Table B'[Date] >= MAX ( 'Table A'[Date] )
            && 'Table B'[Date] <= MAX ( 'Table B'[Date] )
    )
)

Date meet target = 
VAR _Target =
    SELECTEDVALUE ( Parameter[Parameter] )
RETURN
    CALCULATE (
        MIN ( 'Table B'[Date] ),
        FILTER ( ALL ( 'Table B'[Date] ), [Running Total] >= _Target )
    )

Result is as below.

Here I select 200 in slicer. So Item A will meet this target in 2022/09/01 and Item will meet this target in 2022/10/01.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.