Forum Discussion

Ras_Ile's avatar
Ras_Ile
Advocate I
2 years ago
Solved

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 C= CALCULATE(B, USERELATIONSHIP(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 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.