cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
NickTT
Helper II
Helper II

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

 

1 ACCEPTED SOLUTION
v-robertq-msft
Community Support
Community Support

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:

v-robertq-msft_0-1622533048401.png

 

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.

View solution in original post

3 REPLIES 3
NickTT
Helper II
Helper II

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
Community Support
Community Support

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:

v-robertq-msft_0-1622533048401.png

 

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.

View solution in original post

PaulDBrown
Super User II
Super User II

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

 

result.JPG

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors