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
Syk's avatar
Syk
Icon for Resident Rockstar rankResident Rockstar
8 months ago

You can do this in a regular measure, sure. BUT! It's a lot easier in a visual calculation!
Right click on your table and hit 'New visual calculation' then versus previous.
You should already have 90% of what you want once you plug in the fields but if you want that first date not to be included make sure it looks like this: