Forum Discussion

jgeisslinger's avatar
jgeisslinger
Regular Visitor
6 years ago
Solved

Use Max date value to populate future dates

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 i...
Need Help
  • V-lianl-msft's avatar
    V-lianl-msft
    6 years ago

    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's avatar
V-lianl-msft
Community Support
6 years ago

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's avatar
jgeisslinger
Regular Visitor
6 years ago

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's avatar
    V-lianl-msft
    Community Support
    6 years ago

    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.

  • V-lianl-msft's avatar
    V-lianl-msft
    Community Support
    6 years ago

    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.