Fill Future Dates in Date Dimension Table and then filter using 3rd Dimension Table

Hi Anonymous 

I have a test in your sample. Please try this code.

M_Capacity = 
VAR _LASTDATE = CALCULATE(MAX(FACT_BackupStorage_Data[Date]),ALL(FACT_BackupStorage_Data))
VAR _LASTCAPACITY = CALCULATE(SUM(FACT_BackupStorage_Data[Capacity]),FILTER(ALL(FACT_BackupStorage_Data),FACT_BackupStorage_Data[Date] = _LASTDATE))
VAR _FirstEOLDate = MIN(DIM_BackupStorage_Nodes[EOL Date])
VAR _FirstEOLHOST = CALCULATE(MAX(DIM_BackupStorage_Nodes[Host]),FILTER(ALL(DIM_BackupStorage_Nodes),DIM_BackupStorage_Nodes[EOL Date] = _FirstEOLDate))
VAR _CAPACITYAFTEREOL = CALCULATE(SUM(FACT_BackupStorage_Data[Capacity]),FILTER(ALL(FACT_BackupStorage_Data),AND(FACT_BackupStorage_Data[Date] = _LASTDATE,FACT_BackupStorage_Data[Host]<>_FirstEOLHOST)))
VAR _IF = IF(MAX(DIM_Date[Date])<=_LASTDATE,SUM(FACT_BackupStorage_Data[Capacity]),IF(MAX(DIM_Date[Date])<_FirstEOLDate,_LASTCAPACITY,_CAPACITYAFTEREOL))
VAR _RESULT = IF(_IF = BLANK(),BLANK(),_IF&""&"GB")
RETURN
_RESULT

Result is as below.

Best Regards,
Rico Zhou

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