Forum Discussion

jayceeb's avatar
jayceeb
Helper I
3 years ago
Solved

Variance between filtered date

Hi, How to get the variance for filtered dates.

example :

 

DateTotal
Jan5477604
Feb5469004
  
Variance-8,600

 

 

 

powerbi forum -
  • tamerj1's avatar
    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

9 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    Hi jayceeb 
    I suppose you don't have a date table and the month shown in the visual and the slicer is coming the automatic time intelligence hierarchy?

  • jayceeb's avatar
    jayceeb
    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's avatar
      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