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
phpimran's avatar
phpimran
New Member
8 months ago

I think this can be easily handled using DATEADD function. Below is the code I have used in my sample dataset to calculate variance vs previous day:

 

Daily_Variance =
VAR CurrentValue = SUM(DailyVar[Daily Value])
VAR PreviousValue =
CALCULATE(
    SUM(
        DailyVar[Daily Value]
    ),
    DATEADD(
        DailyVar[Order Date],
        -1,
        DAY
    )
)
RETURN
CurrentValue - PreviousValue
 
My sample data set
Order DateDaily Value
12/10/2025178
12/11/2025192
12/12/2025102
12/13/2025182
12/14/2025109
12/15/2025187
12/16/2025155
12/17/2025119
12/18/2025172
12/19/2025120

 

Power BI result set:

I hope this helps.

Thanks.