cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

## Create a % difference measure combining two date tables

Hello Community,
Here is a tricky one that I hope someone can help me figure out.
It sounds really complicated when you read it, but I am happy to share a non-sensitive pbix. where it is pretty obvious what my issue is.

I will try to explain as best I can..

I am creating a report that is using a duplicate date table in order to be able to have dynamic measures based on the date I select in my duplicate date table.

So far I have been able to create measures that show different periodic values depending on what time I select in my duplicate date table.

For example, I use this expression to calculate the sum of the period I have selected, as well as the same period last year:

SelectedPeriod=
CALCULATE(
[Sum], USERELATIONSHIP(f_factTable[Date],Duplicate_DateTable[Date]),
Duplicate_DateTable[DateSelect]=SELECTEDVALUE(Duplicate_DateTable[DateSelect]))

SelectedPeriodLastYear =

VAR XY = CALCULATE([Sum],SAMEPERIODLASTYEAR(d_DateTable[Date]))
VAR XZ = CALCULATE(XY, USERELATIONSHIP(f_factTable[Date],x_Duplicate_DateTable[Date]),DATESBETWEEN(x_Duplicate_DateTable[Date],edate(MAX(Duplicate_DateTable[Date]),-12),edate(MAX(x_Duplicate_DateTable[Date]),-12)))
RETURN XZ
)

Both of these measures work fine. The issue is when I try to create a % difference between the two and adding -1 to get the difference. It simply always returns just -1 in that case, which is so strange to me. I am able to divide without subtracting to get a return value of 92%: but if I add -1 to the DAX expression, the expression only returns the value -1.

In my formula below, I have gotten so far that the variable "vsLY" will produce 0,92 - but the variable "vsLY%" will produce the value -1.

SelectedPeriod_vsLastYear =
VAR A = CALCULATE([Sum], USERELATIONSHIP(f_factTable[Date],Duplicate_DateTable[Date]),Duplicate_DateTable[DateSelect]=SELECTEDVALUE(Duplicate_DateTable[DateSelect]))
VAR B = CALCULATE([Sum],SAMEPERIODLASTYEAR(d_DateTable[Date]))
VAR CCALCULATE(BUSERELATIONSHIP(f_factTable[Date],Duplicate_DateTable[Date]),DATESBETWEEN(Duplicate_DateTable[Date],edate(MAX(Duplicate_DateTable[Date]),-12),edate(MAX(Duplicate_DateTable[Date]),-12)))
VAR vsLY = DIVIDE(C,A)
VAR vsLY% =vsLY - 1

Can anyone help me understand this?
I am happy to share a pbix, which will make it much easier to understand the issue...

Best regards,
Rasmus

1 ACCEPTED SOLUTION
Community Support

Hi @Ras_Ile

Ensure that the context in which the calculation is being performed is correct. The 'USERELATIONSHIP' function changes the active relationship for the calculation, so make sure that the relationships are set up correctly and that they are not conflicting with other filters in your report.

If that doesn't solve the problem, please provide your PBIX file for in-depth analysis.

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support

Hi @Ras_Ile

Ensure that the context in which the calculation is being performed is correct. The 'USERELATIONSHIP' function changes the active relationship for the calculation, so make sure that the relationships are set up correctly and that they are not conflicting with other filters in your report.

If that doesn't solve the problem, please provide your PBIX file for in-depth analysis.

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors