Forum Discussion
Solved
Problem with USERELATIONSHIP together with DATESMTD
Hi,
I am trying to create a measure the calculates the running total of a value, if the date is past "today" it should show blank.
My current measure shows a correct running total but I can't get make it show blank for the dates after today, for those dates it shows the "max" of the value.
My "base"-measure looks like this:
ValueRunningTotal =
VAR value=
CALCULATE (
SUM ( 'projects'[Value] ),
'projects'[category] = "Xyz123",
USERELATIONSHIP ( 'projects'[DoneDate], 'Master Time Table'[Date] ),
DATESMTD ( 'Master Time Table'[Date] )
)
RETURN
value
The projects table has no active relationship to the master time table which I believe is part of the problem. I tried adding this part in the revenue variable under DATESMTD;
FILTER( 'Master Time Table', 'Master Time Table'[Date] <= TODAY() )
It works in the sence that the correct dates are filtered, but instead I loose the running total part and it just shows the daily value without adding them up.
Does anybody have an idea of how I can make the measure work?
Thanks in advance!
Wrap it in an IF, e.g.
ValueRunningTotal = IF ( MIN('Master Time Table'[Date]) <= TODAY(), VAR value= CALCULATE ( SUM ( 'projects'[Value] ), 'projects'[category] = "Xyz123", USERELATIONSHIP ( 'projects'[DoneDate], 'Master Time Table'[Date] ), DATESMTD ( 'Master Time Table'[Date] ) ) RETURN value )
2 Replies
Wrap it in an IF, e.g.
ValueRunningTotal = IF ( MIN('Master Time Table'[Date]) <= TODAY(), VAR value= CALCULATE ( SUM ( 'projects'[Value] ), 'projects'[category] = "Xyz123", USERELATIONSHIP ( 'projects'[DoneDate], 'Master Time Table'[Date] ), DATESMTD ( 'Master Time Table'[Date] ) ) RETURN value )Big thanks! I did not know you could wrap the whole thing in an IF-statement like that. I only tried to wrap the revenue-variable itself in an IF-statement without any luck.
