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

Your sample data doesn't seem to match the data from the desired outcome (regardless of expiry)  I see 3TB usage rather than 500GB - can you please clarify?

 

I am also pretty sure that you should not need the inactive relationship. That can be covered by a measure calculation instead.

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!