Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have two tables; one with the current record and a second table with the history of all the changes. I want to compare the difference between the first and last record. There is a relationship betwen the Curent Detail record on ID to the History table on ID. I have a Date table that the current detail record is linked to on when the report was writen (Report Date). I then have a second inactive relation ship from my Date table to my History table on Version Date.
I am trying to use the following to get my Start Value but it isn't returning correctly if I have "Type" returned in my table visual. I want to show only the value of the First Record. Not the first record of each "Type". If I try to use ALL its giving me all IDs and not the one the report is currently filtered to.
Start =
SUMX (
VALUES('History'),
CALCULATE(SUM('History'[Total]),FIRSTDATE(Dates[Date]),USERELATIONSHIP(Dates[Date],'History'[Version Date]))
)
History Table
ID | Version Date | Type | Total |
1 | 10/1/2010 | Draft | $5 |
1 | 10/2/2010 | Submited | $10 |
1 | 10/3/2010 | Updated | $25 |
2 | 10/1/2010 | Draft | $1 |
2 | 10/3/2010 | Submited | $5 |
2 | 10/5/2010 | Updated | $12 |
Results Expected:
ID | Version Date | Type | Total | Start |
1 | 10/1/2010 | Draft | $5 | $5 |
1 | 10/2/2010 | Submited | $10 | $5 |
1 | 10/3/2010 | Updated | $25 | $5 |
2 | 10/1/2010 | Draft | $1 | $1 |
2 | 10/3/2010 | Submited | $5 | $1 |
2 | 10/5/2010 | Updated | $12 | $1 |
Solved! Go to Solution.
Hi, @NickTT
According to your description, I can clearly understand your requirement, I think you can create a calculated column like this to achieve your requirement:
Start =
VAR _startdate =
MINX (
FILTER ( ALL ( 'History Table' ), [ID] = EARLIER ( 'History Table'[ID] ) ),
[Version Date]
)
RETURN
SUMX (
FILTER (
ALL ( 'History Table' ),
[ID] = EARLIER ( 'History Table'[ID] )
&& [Version Date] = _startdate
),
[Total]
)
And you can get what you want, like this:
You can download my test pbix file below
Thank you very much!
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I ended up using this calculated column method as it made some other functions I also needed to do easier. Thanks for the help!
Hi, @NickTT
According to your description, I can clearly understand your requirement, I think you can create a calculated column like this to achieve your requirement:
Start =
VAR _startdate =
MINX (
FILTER ( ALL ( 'History Table' ), [ID] = EARLIER ( 'History Table'[ID] ) ),
[Version Date]
)
RETURN
SUMX (
FILTER (
ALL ( 'History Table' ),
[ID] = EARLIER ( 'History Table'[ID] )
&& [Version Date] = _startdate
),
[Total]
)
And you can get what you want, like this:
You can download my test pbix file below
Thank you very much!
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Try:
Min Date Value =
VAR MinDate =
CALCULATE (
MIN ( 'History Table'[Version Date] ),
ALLEXCEPT ( 'History Table', 'History Table'[ID] )
)
RETURN
CALCULATE (
[Sum Total],
FILTER (
ALLEXCEPT ( 'History Table', 'History Table'[ID] ),
'History Table'[Version Date] = MinDate
)
)
Proud to be a Super User!
Paul on Linkedin.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |