Sum values from different Time variables.

[luispadillazumb]

Hello, i have several meassures that give me some data and i use them to calculate SLA in terms of:

Requirement (transactions) - production (what we can handle) = requirement (100% or less / how many transactions are left.

As you can see below we have Forecast Final (FC FF) - Prod = Txns OFF SLA

But now in order to have as realistis SLA as possible, i need to add the transactions left to the FF for the following hour. I tried to create a rolling sum but it just continues adding up until the end.

So: FC FF + (Trxns OFF SLA from previous hour) - Prod = Actual Trxns OFF SLA

and so on with all hours.

Is this possible?

[v-yiruan-msft · Accepted solution]

Hi @luispadillazumb ,

You can create a measure as below to get Actual Trxns OFF SLA:

Actual Trxns OFF SLA = 
VAR _curtime =
    SELECTEDVALUE ( 'Requirement'[data Time] )
VAR _pretime =
    CALCULATE (
        MAX ( 'Requirement'[data Time] ),
        FILTER ( ALLSELECTED ( 'Requirement' ), 'Requirement'[data Time] < _curtime )
    )
VAR _preTxnSLA =
    CALCULATE (
        [Txns OFF SLA],
        FILTER ( ALLSELECTED ( 'Requirement' ), 'Requirement'[data Time] = _pretime )
    )
RETURN
    IF ( ISBLANK ( _preTxnSLA ), BLANK (), [FC FF] + _preTxnSLA - [Prod] )

Best Regards

[ryan_mayu]

@luispadillazumb 

could you please provide some sample data and expected output?

[amitchandak]

@luispadillazumb , Assuming you have a measure [Trxns OFF SLA]. You need to try

calculate([Trxns OFF SLA], filter(all(Table) , Table[Datetime] = max(Table[Datetime]) -time(1,0,0)))

[luispadillazumb]

Hi @amitchandak, thank you so much for your support, but some values are missing (in blue):

Have any idea why?