topic Re: Problem with USERELATIONSHIP together with DATESMTD in DAX Commands and Tips

Problem with USERELATIONSHIP together with DATESMTD

Jodallen123 — Wed, 26 Mar 2025 09:43:01 GMT

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!

Re: Problem with USERELATIONSHIP together with DATESMTD

sjoerdvn — Wed, 26 Mar 2025 10:53:35 GMT

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 )

Re: Problem with USERELATIONSHIP together with DATESMTD

Jodallen123 — Wed, 26 Mar 2025 11:49:14 GMT

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.