Variance between filtered date

[jayceeb]

Hi, How to get the variance for filtered dates.

example :

Date	Total
Jan	5477604
Feb	5469004
Variance	-8,600

[jayceeb]

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 · Accepted solution]

@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]

Hi could you please help with the percentage. for this calculated months? 

[tamerj1]

@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 )

[jayceeb]

Hi. I tried but all shows zero %.

[tamerj1]

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

[jayceeb]

OMG. Thanks master. 😅🙏

[jayceeb]

It works. Thank you very much 😭

[tamerj1]

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?