Running total with inactive relationship

[jon_w]

I clearly have some way to go before I understand DAX, but I'm struggling to get a running total when using an inactive date relationship. This is my DAX:

zTest - Running Total 2 =

CALCULATE(

    '_Measures'[Contact: Active Count],

    USERELATIONSHIP('Contact Acquired'[Acquisition Date], 'Date Dimension'[Calendar Date]),

    FILTER(

        ALL('Date Dimension'),

        'Date Dimension'[Calendar Date] <= MAX('Date Dimension'[Calendar Date])

        && 'Date Dimension'[Calendar Date] <= MAX('Contact Acquired'[Acquisition Date])

    ),

    'Contact Acquired'

)

Which works in that it gives me a running total, but what it doesn't do is stop at the max acquisition date - it returns the final total for all dates in the Date Table, which also means visuals exceed resources. This is my model, at least the part I'm using here:

Essentially the contact acquired table is a Factless Fact table used to store the date we acquired a contact. I've done some reading on expanded tables and theory behind DAX, but still can't wrap my head around it. Even Chat GPT has failed to give me a working DAX statement - any pointers greatly appreciated!

[amitchandak]

@jon_w , Try with these two measures

Measure 1= CALCULATE('_Measures'[Contact: Active Count],
USERELATIONSHIP('Contact Acquired'[Acquisition Date], 'Date Dimension'[Calendar Date]))

zTest - Running Total 2 =
CALCULATE(
[Measure 1],
FILTER(
ALL('Date Dimension'),
'Date Dimension'[Calendar Date] <= MAX('Date Dimension'[Calendar Date])
&& 'Date Dimension'[Calendar Date] <= MAX('Contact Acquired'[Acquisition Date])
)
)

[jon_w]

@amitchandak thank you for the prompt response - but that still returns dates beyond the max acquisition date:

[TobyNye]

That's how cumulative totals work, as they are a sum of all the previous months, every month will have a cumulative value as long as one before it had one. If you want to remedy this you could do it in the measure like this:
IF(ISBLANK('_Measures'[Contact: Active Count]), BLANK(), /*The rest of that cumulative total measure*/)

this isn't ideal because it will also return blanks within the date range where you may not want it to be blank. I'd suggest adding a column to your Date Dimension table:

Past/Present = IF('Date Dimension'[Calendar Date] <= NOW(), 1, 0)

You can then apply this filter to either the visual or within your measure and you will only see the total for dates that have happened. Hope this helps, let me know if you have any issues or further questions.

[jon_w]

thanks @toby but when I've set this up before use a date table with an already active relationship I didn't have this issue e.g.

Count of Contact (Current Period) running total in Acquistion Date =

CALCULATE(

    [Count of Contact (Current Period)],

    FILTER(

        ALLSELECTED('Acquisition Date'[Acquistion Date]),

        ISONORAFTER('Acquisition Date'[Acquistion Date], MAX('Acquisition Date'[Acquistion Date]), DESC)

    )

)

Give a running count up to the last date and then stops:

[TobyNye]

Yeah that would make sense, effectively you can use inactive relationships in a measure and that specific measure will use the specified inactive relationship. However, the table with your date field is not using it, so it will display all of the values that it has in it's date column as there is no actual filter context outside of the measure where the relationship is specified. I don't think adding a past/present column would be a problem but if you'd rather not I'd probably need to see the whole data model to try and streamline it a bit so that the relationship can be active and you can get the behavior you're after without the need for a past/present column.

[jon_w]

Yep, but in my original query I make that relationship active:

zTest - Running Total 2 =

CALCULATE(

    '_Measures'[Contact: Active Count],

    USERELATIONSHIP('Contact Acquired'[Acquisition Date], 'Date Dimension'[Calendar Date]),

    FILTER(

        ALL('Date Dimension'),

        'Date Dimension'[Calendar Date] <= MAX('Date Dimension'[Calendar Date])

        && 'Date Dimension'[Calendar Date] <= MAX('Contact Acquired'[Acquisition Date])

    ),

    'Contact Acquired'

)

So I'm not sure why it's not applying this filter:
'Date Dimension'[Calendar Date] <= MAX('Contact Acquired'[Acquisition Date]

My Date dimension is linked to 5 other tables so I'm unable to make that relationship active in the model, I have considered creating a separate Date Dimension table, but I feel like it should be possible without that. Also, feels like a good exercise to understand how DAX actually works.

[jon_w]

So I've got this working through the use of some variables and an IF statement, but it feels clunky - I can't believe this is the best way to achieve this:

zTest - Running Total 3 =

VAR _LastAcquisitionDate =

    CALCULATE(

        MAX('Contact Acquired'[Acquisition Date]),

        REMOVEFILTERS('Contact Acquired')

    )

VAR _LastVisibleDate =

    MAX('Date Dimension'[Calendar Date])

VAR _isVisible =

    _LastVisibleDate <= _LastAcquisitionDate

RETURN

IF (

    _isVisible,

    CALCULATE(

        DISTINCTCOUNT('Contact Acquired'[contactid]),

        USERELATIONSHIP('Date Dimension'[Calendar Date], 'Contact Acquired'[Acquisition Date]),

        FILTER(

            ALLSELECTED('Date Dimension'[Calendar Date]),

            ISONORAFTER('Date Dimension'[Calendar Date], MAX('Date Dimension'[Calendar Date]), DESC)

        ),

        'Contact Acquired'[Acquisition Date]

    )

)