Hi, How to get the variance for filtered dates.
example :
Date | Total |
Jan | 5477604 |
Feb | 5469004 |
Variance | -8,600 |
Solved! Go to Solution.
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
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
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
Hi could you please help with the percentage. for this calculated months?
@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 )
Hi. I tried but all shows zero %.
@jayceeb
Change the data type to %.
However, this gives you the percentage difference between the two selected months if this is what you want.
OMG. Thanks master. 😅🙏
It works. Thank you very much 😭
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?