Calculate future expiry of product

[julien-rivley]

Hello,

I'm trying to calculate the future stock expiry for each of the future months with the following data:

- current month opening stock by expiry date
- demand of the current and all the future months

I believe to have found a way to calculate it to calculate it but I'm stuck at combining 2 table, here are the details
Current month opening stock table by expiry date:

YearMonth	Current month stock by expiry date
Jan-20	1000
Mar-20	450
May-20	1000
Oct-20	100

Demand table:

YearMonth	Demand	Cumulated Demand
Jul-19	100	100
Aug-19	100	200
Sep-19	100	300
Oct-19	100	400
Nov-19	100	500
Dec-19	100	600
Jan-20	100	700
Feb-20	100	800
Mar-20	100	900
Apr-20	100	1000
May-20	100	1100
Jun-20	100	1200
Jul-20	100	1300
Aug-20	100	1400
Sep-20	100	1500
Oct-20	100	1600
Nov-20	100	1700
Dec-20	100	1800
Jan-21	100	1900
Feb-21	100	2000
Mar-21	100	2100

What I would like:

YearMonth	Current month stock by expiry date	Cumulated demand
Jan-20	1000	700
Mar-20	450	900
May-20	1000	1100
Oct-20	100	1600

My issue is that "YearMonth" in the 2 tables are not related in the data model... is there any way to join these 2 tables? I tried NATURALLEFTOUTERJOIN but it doesn't seem to work.

Thanks,

Julien

[dearwatson · Accepted solution]

Hi Julien, 

You can build a measure that first gets the "CurrentYearMonth" then filters the second table to return only that row and sum the cumulated demand:

Cumulated Demand = 
VAR CurrentYearMonth =
    MAX ( Table1[YearMonth] )
RETURN
    CALCULATE (
        SUM ( Table2[Cumulated Demand] ),
        FILTER ( Table2, Table2[YearMonth] = CurrentYearMonth )
    )

[julien-rivley]

Thanks for the help!