cancel
Showing results for
Did you mean: Frequent Visitor

## Variance between filtered date

Hi, How to get the variance for filtered dates.

example :

 Date Total Jan 5477604 Feb 5469004 Variance -8,600  1 ACCEPTED SOLUTION  Super User

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 9 Frequent Visitor

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   Super User

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 Frequent Visitor  Super User

@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 )`````` Frequent Visitor

Hi. I tried but all shows zero %.   Super User

@jayceeb
Change the data type to %.
However, this gives you the percentage difference between the two selected months if this is what you want. Frequent Visitor

OMG. Thanks master. 😅🙏 Frequent Visitor

It works. Thank you very much 😭  Super User

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?  