topic Re: Trying to get the difference between two measure values using DAX in DAX Commands and Tips

Trying to get the difference between two measure values using DAX

charlie7491 — Wed, 15 Jun 2022 18:18:06 GMT

I will like to get the difference between two measure values using DAX

AADPMAU_Increase = [AADPMAU_End] - [AADPMAU_Start]

But when I do the subtraction, I am getting 0

I have tried different things, but nothing is working

The two values are start and end values that are set by date selector filters. It appears that the calculation is always treating both as equal therefore returning 0

AADPMAU_Start = CALCULATE(AADPTable[AADPMAU_Sum],FILTER(DimDate,DimDate[StartDate] = DimDate[StartDate]))

AADPMAU_End = CALCULATE(AADPTable[AADPMAU_Sum],FILTER(DimDate,DimDate[EndDate] = DimDate[EndDate]))

AADPMAU Increase =
var at_end = AADPTable[AADPMAU_End]
var at_start = AADPTable[AADPMAU_Start]
return at_end - at_start

Re: Trying to get the difference between two measure values using DAX

Whitewater100 — Wed, 15 Jun 2022 19:34:09 GMT

Hi:

You can try the following. Assuming your date slicer is from date table.

startdate = FIRSTDATE(Dates[Date])

Enddate = LASTDATE(Dates[Date])

Days Diff = INT([Enddate] - [startdate])

You may need to adjust the table names to match, but this is the jist of it. Hope this helps.

Re: Trying to get the difference between two measure values using DAX

charlie7491 — Wed, 15 Jun 2022 20:44:55 GMT

I need the AADPMAU Increase in the values associated with the date changes (and not the difference in dates)

Do you have any suggestion how I can use your approach to calculate the difference?

Re: Trying to get the difference between two measure values using DAX

Whitewater100 — Wed, 15 Jun 2022 20:59:56 GMT

Hi:

I can check it out but ask for some data , like you are working with.

Even if it's excel with made up info it's fine.

Thanks..

Re: Trying to get the difference between two measure values using DAX

charlie7491 — Wed, 15 Jun 2022 21:07:09 GMT

I have the sample PBIX file with the problem scenario but I see no way to send that file to you. Do you know how I may be able to send it to you?

Re: Trying to get the difference between two measure values using DAX

Whitewater100 — Wed, 15 Jun 2022 23:35:26 GMT

Hi:

If yu use dropbox or google drive you can upload to those site and grab a link and paste it into your message.

If you want you can send to my email skelleybill@gmail.com

Thanks.

Re: Trying to get the difference between two measure values using DAX

charlie7491 — Mon, 20 Jun 2022 00:35:50 GMT

Thanks so much. I have found the solution. I will post it here soon, hopefully

Re: Trying to get the difference between two measure values using DAX

charlie7491 — Sun, 26 Jun 2022 23:51:06 GMT

The solution is to use the User Relationships approach.

1. You have to put the StartDate and EndDate fields into two separate tables (for example DimDate and DimEndDate, respectively).

2. Make both of these tables have a relationship with the main table that have the values you are looking to filter based on the StartDate and EndDate slicers
In our example, that table and field to be filtered is AADPTable[AADPMAU_Sum]

3. Since you can only have one active relationship at a time, you make the relationship between the DimDate and AADPTable the active one, with a relationship based on the FactDate field
AND you make the relationship between the DimEndDate and AADPTable the INACTIVE one, also with a relationship based on the FactDate field

4. Having set things up as described above,

The AADPMAU_Sum value associated with the StartDate slicer would be as follows:

AADPMAU_Start = sum(AADPTable[AADP MAU])

The AADPMAU_Sum value associated with the EndDate slicer would be as follows:

AADPMAU_End =

CALCULATE(

AADPTable[AADPMAU_Sum],

ALL(DimDate),

USERELATIONSHIP(DimEndDate[EndDate],AADPTable[FactDate])

)

This article explains User Relationships Using USERELATIONSHIP in DAX - SQLBI