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.

camargos88's avatar
camargos88
Community Champion
6 years ago

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