topic Re: Variance between filtered date in DAX Commands and Tips

Variance between filtered date

jayceeb — Mon, 22 May 2023 10:33:11 GMT

Hi, How to get the variance for filtered dates.

example :

Date	Total
Jan	5477604
Feb	5469004

Variance	-8,600

Re: Variance between filtered date

tamerj1 — Mon, 22 May 2023 11:27:43 GMT

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?

Re: Variance between filtered date

jayceeb — Wed, 24 May 2023 10:28:46 GMT

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

Re: Variance between filtered date

tamerj1 — Wed, 24 May 2023 11:10:34 GMT

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

Re: Variance between filtered date

jayceeb — Wed, 24 May 2023 11:31:54 GMT

It works. Thank you very much 😭

Re: Variance between filtered date

jayceeb — Wed, 31 May 2023 05:20:29 GMT

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

Re: Variance between filtered date

tamerj1 — Wed, 31 May 2023 06:36:59 GMT

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 )

Re: Variance between filtered date

jayceeb — Thu, 01 Jun 2023 09:52:54 GMT

Hi. I tried but all shows zero %.

Re: Variance between filtered date

tamerj1 — Thu, 01 Jun 2023 10:05:29 GMT

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

Re: Variance between filtered date

jayceeb — Thu, 01 Jun 2023 10:14:20 GMT

OMG. Thanks master. 😅🙏