Cumulative measure based on two date columns

[Anonymous]

Hi everyone!

I'm working on a datamart that contains data of Virtual Machines resources utilization. I'm trying to build a historycal visualization of this resources, based on two dates: the Create Date of the VM and its Delete Date.

What I want to do is show the cumulative resources consumption while this VM's were actives. I have this two dates written above, so I know the period that a VM were active.

I am currently using a cumulative measure to calculate this values, but I don't want to sum the resources of a inactive VM, so the formula must not consider a resource's value when a Delete Date is filled.

Example:

My measure is resulting this:

Measure:

Cumulative =

CALCULATE (

     SUM(ftInstances[qtRam])/1024;

     FILTER (

         ALLSELECTED ( 'Date' );

          'Date'[skDate] <= MAX ( ftInstances[skDate] )

   )

)

What I want is something like this:

Thanks!

[avanderschilden]

Hi Andre,

Did you try to add a second filter within CALCULATE?

Cumulative =

CALCULATE (

     SUM(ftInstances[qtRam])/1024;

     FILTER (

         ALLSELECTED ( 'Date' );

          'Date'[skDate] <= MAX ( ftInstances[skDate] )

   )

,NOT(ISBLANK('ftInstances'[DeletedDate]))
)

Regards,

Adrian

)