Forum Discussion

topazz11's avatar
topazz11
Icon for Helper III rankHelper III
8 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
    8 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
GeraldGEmerick's avatar
GeraldGEmerick
Icon for Memorable Member rankMemorable Member
8 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