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.

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
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.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Thank you very much lbendlin  & v-yalanwu-msft , both of your solutions worked brilliantly! I really appreciate you taking the time to respond and provide the measures, and will now be able to use and adapt these going forward!