Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
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"
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
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.