TOTALYTD not working when using USERELATIONSHIP

[javirmerino]

Hi everyone, i'm trying to calculate a running total count of completed incidents for the year, based on the CompletionDate. However, the primary relationship between my date dimensions table and the incidents table is based on the RequestDate. The result is my figures are slightly incorrect as i'm calculating the number of completed requests raised in 2020 rather than the number of completed requests in 2020. Subtle but the discrepency is large enough to affect my analysis.

i know you're able to use the USERELATIONSHIP to declare a secondary link but when i try this with TOTALYTD i'm advised that this can only be used in the CALCULATE function.

How can i combine the CALCULATE, USERELATIONSHIP and TOTALYTD functions together to get the cumulative sum over time?

Thanks in advance for any solutions and my apologies if its been answered already. I did have a bit of a search but couldnt find a solution.

A

[sturlaws · Accepted solution]

Hi @javirmerino, 

how have you tried to incorporate USERELATIONSHIP?

I just tried this:

ytd = TOTALYTD(sum('Table'[sales]);dates[Date];USERELATIONSHIP('Table'[date2];dates[Date]))

and it works as it should

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

[javirmerino]

Thanks for your response, @sturlaws . This resolved my issue.

I feel stupid but i'll admit the issue wasnt in my DAX - i'd created an invalid relationship between two similarly named fields. Schoolboy error!