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.
Hi,
lets assume that we have two data tables A and B where both holding date fields and are linked on date field
Table A: Total actual companies inventory for all past months
Table B: Factory inventory for all past AND future months (planned)
I used a measure to calculate the difference between Company Inventory and Factory Inventory = Import Inventory. Of course this is only done for past months.
Lets assume that for planning purposes the Import Inventory will not change for future months until an actual inventory is available and we can calculate the correct value. So now I want to enhance the calculation to use the Max Date Import Value to populate all future entries which will enable me to backwards forecast the future company inventory.
So the formula should look like something like:
IF(Company Inventory - Factory Inventory<=0; Latest Import Inventory; Company Inventory - Factory Inventory)
So the red part is not working for me. I tried several different formulas like Lastdate, Max Date with ALL, ALLSelected etc. but somehow it only calculates the latest value only and I dont know how to populate future rows.
Date | Company Inventory | Factory Inventory | Import Inventory |
Jan 2020 | 100 | 50 | 50 |
Feb 2020 | 110 | 60 | 50 |
Mar 2020 | 90 | 30 | 60 |
Apr 2020 | 40 | 60 | |
May 2020 | 90 | 60 |
Makes that sense? Thanks for help!
Solved! Go to Solution.
Hi @jgeisslinger ,
Is this problem sloved?
I recreated measure and updated the sample pbix.
Measure2 =
var i_i = SUM(Sheet2[Company Inventory])-SUM(Sheet3[Factory Inventory])
VAR LAST_DATE = CALCULATE(FORMAT(MAX(Sheet2[Date]),"yyyy-mm"),ALL(Sheet2))
VAR LAST_C_I = CALCULATE(SUM(Sheet2[Company Inventory]),FILTER(ALL(Sheet2),FORMAT(Sheet2[Date],"yyyy-mm")=LAST_DATE))
VAR LAST_I_I = CALCULATE(LAST_C_I-SUM(Sheet3[Factory Inventory]),FILTER(ALL(Sheet3),FORMAT(Sheet3[Date],"yyyy-mm")=LAST_DATE))
RETURN IF(i_i<0,LAST_I_I,i_i)
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @jgeisslinger ,
You can refer to this measure, maybe there is a simpler way.
Measure =
var i_i = MAX(Sheet2[Company Inventory])-MAX(Sheet3[Factory Inventory])
VAR LAST_DATE = CALCULATE(MAX(Sheet2[Date]),ALL(Sheet2))
VAR LAST_C_I = CALCULATE(MAX(Sheet2[Company Inventory]),FILTER(ALL(Sheet2),Sheet2[Date]=LAST_DATE))
VAR LAST_I_I = CALCULATE(LAST_C_I-MAX(Sheet3[Factory Inventory]),FILTER(ALL(Sheet3),Sheet3[Date]=LAST_DATE))
RETURN IF(i_i<0,LAST_I_I,i_i)
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
thank you for your replies. I tried both and maybe I missed some information important to understand the issue:
1. The table A holding the company inventory contains not one but multiple values per month as it has the inventory per plant and product. So basically the total per month is the SUM of the column inventory. I adapted the forumla using SUM instead of MAX.
2. The table B holding the factory inventory is formatted in the way that we have one value column and an attribute column. So to get the inventory we must use TableB[Value]; TableB[Attribute]="Inventory". Also here we have multiple entries per month as it is per plant. So we must also use SUM instead of MAX.
After doing adjustments it shows the correct value for past months but still blank for future months. I also extracted the different variables to own measures and the values are calculated correct beside the fact that the Max Date is not correct or filtered by row.
In which home table should be used?
Both tables have different date scope. In the table visual I use the date field from table B as it contains also future dates.
VAR TEST 4 =
var i_i = 'TableA'[VAR Qty in tons]-'TableB'[VAR Inventory Actuals]
var LAST_DATE = CALCULATE(MAX('TableA'[Date]);ALL('TableA'))
var LAST_C_Inventory = CALCULATE('TableA'[VAR Qty in tons];FILTER(ALL('TableA');'TableA'[Date]=LAST_DATE))
var LAST_I_INVENTORY = CALCULATE(LAST_C_Inventory-CALCULATE(SUM('TableB'[Value]);'TableB'[Attribute]="Inventory");FILTER(ALL('TableB');'TableB'[Date]=LAST_DATE))
RETURN
IF(i_i<=0;LAST_I_INVENTORY;i_i)
Thanks!
Hi @jgeisslinger ,
Is this problem sloved?
I recreated measure and updated the sample pbix.
Measure2 =
var i_i = SUM(Sheet2[Company Inventory])-SUM(Sheet3[Factory Inventory])
VAR LAST_DATE = CALCULATE(FORMAT(MAX(Sheet2[Date]),"yyyy-mm"),ALL(Sheet2))
VAR LAST_C_I = CALCULATE(SUM(Sheet2[Company Inventory]),FILTER(ALL(Sheet2),FORMAT(Sheet2[Date],"yyyy-mm")=LAST_DATE))
VAR LAST_I_I = CALCULATE(LAST_C_I-SUM(Sheet3[Factory Inventory]),FILTER(ALL(Sheet3),FORMAT(Sheet3[Date],"yyyy-mm")=LAST_DATE))
RETURN IF(i_i<0,LAST_I_I,i_i)
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @jgeisslinger ,
What is the relationship between the two tables?
Since I can't see your pbix file, you can try to think of a solution like this.
First, calculate the "import inventory"
If you want the future date to display the last "import inventory", first you need to get the month and year of the maximum date of table A.
Then calculate the "company inventory" of the current month, use lastdate as the filter condition of table B to calculate the last "import inventory", and finally use if condition statement.
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @jgeisslinger ,
Try this code to create a new calculated column:
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 |
---|---|
111 | |
97 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |