Forum Discussion

NickTT's avatar
NickTT
Helper III
5 years ago
Solved

First and Last values excluding sub category

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

IDVersion DateTypeTotal
110/1/2010Draft$5
110/2/2010Submited$10
110/3/2010Updated$25
210/1/2010Draft$1
210/3/2010Submited$5
210/5/2010Updated$12

 

Results Expected:

IDVersion DateTypeTotalStart
110/1/2010Draft$5$5
110/2/2010Submited$10$5
110/3/2010Updated$25$5
210/1/2010Draft$1$1
210/3/2010Submited$5$1
210/5/2010Updated$12$1

 

Need Help
  • v-robertq-msft's avatar
    v-robertq-msft
    5 years ago

    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.

3 Replies

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    5 years ago

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

     

     

  • v-robertq-msft's avatar
    v-robertq-msft
    Community Support
    5 years ago

    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.

  • NickTT's avatar
    NickTT
    Helper III
    5 years ago

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