cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Vinush Frequent Visitor
Frequent Visitor

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 trying to find a solution to get the difference between the modified date based on the status Date.

 

 

 

Example

 

Opportunity: SampleA

 

SalesHistory Table

 

Record   Modified            Status

ABC       12 Nov 2019       Open

BCD        14 Nov 2019      Progress

EFG         17 Nov 2019       In Review

HIJ       19 Nov 2019        Closed

 

As per my understanding, the calculated fields can be used at the record level and not at multiple record instances.

Could you please let me know if you have a solution for the same.

3 REPLIES 3
Super User
Super User

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

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


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

mcybulski Member
Member

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

Group on the field that allows the sales history table to merge with the opportunity table. The code below shows the grouping with a sample table in Excel as the sales history table.

 

let
    Source = Excel.CurrentWorkbook(){[Name="SalesHistory"]}[Content],
     #"Grouped Rows" = Table.Group(Source, {"Order"}, {{"Modified", each List.Max([Modified]), type datetime}})
in
    #"Grouped Rows"

 

 

Vinush Frequent Visitor
Frequent Visitor

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

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.

 

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)