Forum Discussion

cbailey92's avatar
cbailey92
New Member
5 years ago

TOTALMTD and USERELATIONSHIP

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

month-to-date
time-intelligence
TOTALMTD
USERELATIONSHIP

1 Reply

  • OwenAuger's avatar
    OwenAuger
    Super User
    5 years ago

    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