Forum Discussion

Helper I

3 years ago

Solved

Variance between filtered date

Hi, How to get the variance for filtered dates. example : Date Total Jan 5477604 Feb 5469004 Variance -8,600

powerbi forum -

tamerj1
3 years ago
jayceeb

Please try

Variance =
VAR MaxMonth =
MAX ( MonthsF[YearMonth] )
VAR MinMonth =
MIN ( MonthsF[YearMonth] )
VAR MaxMonthValue =
CALCULATE ( SUM ( Consumptions[Total] ), MonthsF[YearMonth] = MaxMonth )
VAR MinMonthValue =
CALCULATE ( SUM ( Consumptions[Total] ), MonthsF[YearMonth] = MinMonth )
RETURN
MaxMonthValue - MinMonthValue

Helper I

3 years ago

Hi, I have a Date table shown below.

however I don't know how to get the variance. I'm using only one column for date and I want to select between months. please help

tamerj1
Community Champion
3 years ago
jayceeb

Please try

Variance =
VAR MaxMonth =
MAX ( MonthsF[YearMonth] )
VAR MinMonth =
MIN ( MonthsF[YearMonth] )
VAR MaxMonthValue =
CALCULATE ( SUM ( Consumptions[Total] ), MonthsF[YearMonth] = MaxMonth )
VAR MinMonthValue =
CALCULATE ( SUM ( Consumptions[Total] ), MonthsF[YearMonth] = MinMonth )
RETURN
MaxMonthValue - MinMonthValue
- jayceeb
  Helper I
  3 years ago
  It works. Thank you very much 😭
- jayceeb
  Helper I
  3 years ago
  Hi could you please help with the percentage. for this calculated months?
  - tamerj1
    Community Champion
    3 years ago
    jayceeb
    Do yu mean something like this?
    
    Variance % = VAR MaxMonth = MAX ( MonthsF[YearMonth] ) VAR MinMonth = MIN ( MonthsF[YearMonth] ) VAR MaxMonthValue = CALCULATE ( SUM ( Consumptions[Total] ), MonthsF[YearMonth] = MaxMonth ) VAR MinMonthValue = CALCULATE ( SUM ( Consumptions[Total] ), MonthsF[YearMonth] = MinMonth ) RETURN DIVIDE ( MaxMonthValue - MinMonthValue, MaxMonthValue )

Related content