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
krishnakanth240's avatar
krishnakanth240
Icon for Super User rankSuper User
8 months ago

Hi topazz11 

 

Can you please check this Calculated column and give headsup if it works ,if not please let us know.

 

Daily Variance =
VAR CurrentDate = table[Date]
VAR CurrentValue = table[Value]

VAR PreviousValue =
CALCULATE (
MAX ( table[Value] ),
FILTER (
table,
table[Date] =
CALCULATE (
MAX ( table[Date] ),
FILTER ( table, table[Date] < CurrentDate )
)
)
)

RETURN
IF (
ISBLANK ( PreviousValue ),
0,
CurrentValue - PreviousValue
)