Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Calculating the difference between Dates stored across different records in the same table

Hello All,   We have a sales history Table associated with the Opportunity Table. (1 Opportunity with multiple Sales History) The Sales History table last has a modified date and Status date. I'm ...
Need Help
Greg_Deckler's avatar
Greg_Deckler
Community Champion
6 years ago

Assuming that there is some sort of relationship that ties the two tables together, you could use a column in your Opportunity table something like:

 

 

Days = MAXX(RELATEDTABLE('SalesHistory'),[Modified]) - MINX(RELATEDTABLE('SalesHistory'),[Modified])

 

 

https://docs.microsoft.com/en-us/dax/relatedtable-function-dax

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Thanks for your reply Greg.
    Yes, I can create a column in opportunity. However, I might not be able to use MAXX functions in this scenario.

    My challenge is to find the difference between Modified date based on Status(Open, InReview)
    Hence MAXX function cannot be used here. Since I'm not dealing with the maximum and minimum value.

     

    For example

     

    Status was InReview on 17 Nov and Status was open on 12 Nov.
    The difference between them ie 5 needs to be calculated and stored in Opportunity.