Forum Discussion

topazz11's avatar
topazz11
Icon for Helper III rankHelper III
10 months ago
Solved

daily variance in DAX

How do I calculate the variance as below?       ---i tried to write it and doesnt work. Daily Variance = VAR _maxvalue = CALCULATE ( MAX ( table[Value] ), FILTER ( table, table[Date] ...
Need Help
  • GeraldGEmerick's avatar
    GeraldGEmerick
    10 months ago

    topazz11 I think that the following should work:

    Daily Variance = 
VAR _CurrentDate = MAX( 'Table'[Date] )
VAR _PreviousDate = 
    CALCULATE(
        MAX( 'Table'[Date] ),
        'Table'[Date] = _CurrentDate - 1
    )
VAR _CurrentValue = MAX( 'Table'[Value] )
VAR _PreviousValue = 
    CALCULATE( 
        MAX( 'Table'[Value] ),
        'Table'[Date] = _PreviousDate
    )
VAR _Return = IF( _PreviousDate = BLANK(), 0, _CurrentValue - _PreviousValue )
RETURN _Return
cengizhanarslan's avatar
cengizhanarslan
Icon for Super User rankSuper User
10 months ago

Please check the formula below:

 

Daily Variance =
VAR PrevValue =
    CALCULATE (
        MAX ( 'Table'[Value] ),
        DATEADD ( 'Table'[Date], -1, DAY )
    )
RETURN
MAX ( 'Table'[Value] - PrevValue, 0 )