Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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 ) )
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 ) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |