First and Last values excluding sub category

[NickTT]

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

[NickTT]

I ended up using this calculated column method as it made some other functions I also needed to do easier. Thanks for the help!

[v-robertq-msft · Accepted 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.

[PaulDBrown]

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
        )
    )