Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Fill future dates with latest data and Filter Dates across two tables

Hello,   Hopefully someone can help!   I'm trying to calculate the amount of used storage available in our Vcenter over time, and then forecast this against customer contract termination dates. I...
Need Help
  • lbendlin's avatar
    lbendlin
    4 years ago

    Something like this?

    M_Capacity = 
var d = max(DIM_Date[Date])
var m = CALCULATE(max(FACT_VM[Date]),FACT_VM[Date]<=d)
var s= CALCULATE(sum(FACT_VM[StorageUsageGB]),FACT_VM[Date]=m)
return if(d<SELECTEDVALUE(DIM_Customer[NextTerminationDate]),s)

     

    See attached.

v-yalanwu-msft's avatar
v-yalanwu-msft
Icon for Community Support rankCommunity Support
4 years ago

Hi, Anonymous ;

You could try it.

M_Capacity = 
VAR _LASTDATE = CALCULATE(MAX(FACT_VM[Date]),ALL(Fact_VM))
VAR _LASTCAPACITY = CALCULATE(SUM(FACT_VM[StorageUsageGB]),FILTER(ALL('FACT_VM'),[Customer]=MAX('DIM_Customer'[Customer])&&[Date]=_LASTDATE))
RETURN
 IF(MAX('DIM_Date'[Date (DD)])<=MAX('DIM_Customer'[NextTerminationDate])&&MAX('DIM_Customer'[Customer])<>BLANK(),
  IF( MAX('DIM_Date'[Date (DD)]) in VALUES('FACT_VM'[Date]),
   SUM(FACT_VM[StorageUsageGB]),_LASTCAPACITY))

The final output is shown below:


Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.