TOTALMTD and USERELATIONSHIP

cbailey92 — Sun, 17 Jan 2021 18:30:58 GMT

Hi All,

I am trying to calculate a MTD value based on a date column from my 'Quotes' table that is different to the date column that has an active relationship with my calendar.

I think I need to use some sort of combination of TOTALMTD and USERELATIONSHIP?

I am calculating a value from my 'Quotes' table. The 'Quotes' table has a 'Created Date' column, which is my active relationship with my calendar table, and a 'Quote Won Date', which is what I want my month-to-date calculation to work from.

I do have an inactive relationship established in my model between my calendar table and the 'Quote Won Date'

Here is something I have tried which does not work but might illustrate what I am trying to calculate:

MTD Value of Quotes Won by Quote Won Date = CALCULATE([Value of Quotes Won],DATESMTD('calendar'[Reporting Date]),USERELATIONSHIP(quoted_opportunities_and_quotes_data[Quote Won Date],'calendar'[Reporting Date]))

Really appreciate any help anyone can offer!

Many thanks in advance,

Chris

Re: TOTALMTD and USERELATIONSHIP

OwenAuger — Mon, 18 Jan 2021 01:21:40 GMT

Hi cbailey92

The logic of your measure appears correct to me.

It is simultaneously applying a MTD filter on your 'calendar' table, and activating the relationship with the [Quote Won Date] column.

I tested your measure in a small sample data model and it gave the expected result.

Could you post either a link to a shareable PBIX or some some sample data and the result you are getting?

Regards,

Owen

topic Re: TOTALMTD and USERELATIONSHIP in DAX Commands and Tips

TOTALMTD and USERELATIONSHIP

Re: TOTALMTD and USERELATIONSHIP