topic Re: Subtraction is not working for me in DAX Commands and Tips

Subtraction is not working for me

charlie7491 — Mon, 13 Jun 2022 20:56:31 GMT

I will like to get the difference between two measure' values

#AADP MAU Increase = [#AADP MAU_End] - [#AADP MAU_Start]

Individually, these show as:

#AADP MAU_Start = 166M

#AADP MAU_End = 170M

But when I do the substraction, I am getting 0 or 0.00

I have tried different things, such as the below, but still get 0

#AADP MAU Increase = FORMAT([#AADP MAU_End] - [#AADP MAU_Start], "General Number")

Re: Subtraction is not working for me

johnt75 — Tue, 14 Jun 2022 10:22:46 GMT

Are there different filters being applied to the visuals showing the results ?

Try running the below in DAX Studio

EVALUATE ROW ( "#AADP MAU_Start", [#AADP MAU_Start], "#AADP MAU_End", [#AADP MAU_End], "#AADP MAU Increase", [#AADP MAU Increase], "Simple calc", [#AADP MAU_End] - [#AADP MAU_Start] )

Re: Subtraction is not working for me

charlie7491 — Wed, 15 Jun 2022 02:11:00 GMT

The start and end values come from filters. The Dax formulas are the same but the filters set different results

So it appears that is the issue then.
Do you have any idea how I may trick it into retaining the displayed filter results so that I can get the substraction to be valid?

Re: Subtraction is not working for me

charlie7491 — Wed, 15 Jun 2022 02:34:04 GMT

johnt75 has hinted at what the problem was. The two values are start and end values that are set by filters.

The Dax formulas are the same but the filters set different results. So the calculation is treating both as equal therefore returning 0. The solution is to use something like the below

AADP MAU_Start = calculate(sum(CustomerAndTasksView[AADP MAU]),DimDate[StartDate]=DimDate[StartDate])

AADP MAU_End = calculate(sum(CustomerAndTasksView[AADP MAU]),DimDate[EndDate]=DimDate[EndDate])