Use Max date value to populate future dates

[jgeisslinger]

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!

[V-lianl-msft]

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)

Sample .pbix

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

[jgeisslinger]

Hi @V-lianl-msft @camargos88 

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!

[V-lianl-msft · Accepted 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.

[V-lianl-msft]

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.

[camargos88]

Hi @jgeisslinger ,

Try this code to create a new calculated column:

NewColumn =

IF(

'Table'[Company Inventory] - 'Table'[Factory Inventory] <= 0;

CALCULATE(SUM('Table'[Import Inventory]); FILTER(ALL('Table'[Date]); 'Table'[Date] = EARLIER('Table'[Date])));

'Table'[Company Inventory] - 'Table'[Factory Inventory])

Or you can create with Max Date when it's no blank:

X =

IF(

'Table'[Company Inventory] - 'Table'[Factory Inventory] <= 0;

VAR _date = 'Table'[Date]

VAR _MaxDate = CALCULATE(MAX('Table'[Date]); FILTER(ALL('Table'[Import Inventory]); ISBLANK('Table'[Import Inventory]) = FALSE()))

RETURN CALCULATE(SUM('Table'[Import Inventory]); FILTER(ALL('Table'[Date]); 'Table'[Date] = _MaxDate));

'Table'[Company Inventory] - 'Table'[Factory Inventory])

Ricardo