Make Chart Using Latest ID Based On User Selected Date

[alee5210]

Hi Everyone, I have a tricky question.

I am looking to create a chart which shows which courier carried the most.

To explain how the data works, in the main table below we have a Parcel ID and the date submitted never changes. But our users might update the mass if they remeasure it and find it to be different. So for Parcel ID = 1111, the mass was measured as 100 on 1 Jan, 2024 (effective start date) but after remeasurement on the 14th Jan, 2024 (effective end date) there was an error and it was resubmitted as 105 on the 15 Jan, 2024 (the new effective start date). For the latest record for reach Parcel ID, the effective end date is just put as 31 Dec, 2099 by default.

Parcel ID		Date Submitted		Courier		Mass		Effective Start Date		Effective End Date
1111		01-Jan-24		A		100		01-Jan-24		14-Jan-24
1111		01-Jan-24		A		105		15-Jan-24		31-Dec-99
2222		15-Jan-24		B		35		15-Jan-24		22-Jan-24
2222		15-Jan-24		B		37		23-Jan-24		31-Dec-99
3333		01-Jan-24		C		10		01-Jan-24		14-Jan-24
3333		01-Jan-24		C		100		15-Jan-24		22-Jan-24
3333		01-Jan-24		C		1000		23-Jan-24		31-Dec-99
4444		15-Jan-24		D		50		15-Jan-24		31-Dec-99
5555		29-Jan-24		A		70		29-Jan-24		31-Dec-99
6666		01-Jan-24		B		18		01-Jan-24		31-Dec-99

In the second table, we have a list of dates that is connected to the Date Submitted. Users have a slicer on the dashboard that they can use to control the Date Submitted.

In the third table, we have another list of dates that is not connected to the main dataset. There is a slicer and when a date is selected, the latest record (based on the effective start date) is used.

Here is an image of the data model.

You can see how it is set up currently in my screenshot below. This only shows Parcels that were submitted between the 14 Jan and 31st Jan and only shows the mass for the reporting period.

I have set up a measure called 'Show' which selects the latest record for each ID based on the slicers that the users have selected nad used that in the table as well as a slicer for the table (see slicer panel).

show = 
IF(
    MAX('Test Table'[Effective Start Date] )
        = CALCULATE(
            MAX( 'Test Table'[Effective Start Date] ),
            ALLEXCEPT( 'Test Table', 'Test Table'[Parcel ID] ),
            AND('Test Table'[Effective Start Date] <= MAX( 'Not JoinedDate Table'[Date] ), 'Test Table'[Effective End Date] >= MAX( 'Not JoinedDate Table'[Date] ))
        ),
    "Show"
)

I want to create a bar chart which shows how much each courier carried during that period. E.g. for the following screenshot, I want a bar chart where courier A carries (105+70) 175, courier B carries (37+18) 55, C carries 1000 and D carries 50. Whenever I try make that bar chart it does not appear.

(Using ths show measure)

With my show measure I am getting something completely different, how do I achieve the desired outcome?

[v-zhengdxu-msft · Accepted solution]

Hi @alee5210 

Please try this:

First of all, I create a measure:

Effective =
VAR _currentDate =
    MAX ( 'Table'[Effective Start Date] )
VAR _currentCountier =
    SELECTEDVALUE ( 'Table'[Courier] )
VAR _currentID =
    MAX ( 'Table'[Parcel ID] )
VAR _vtable =
    FILTER (
        ADDCOLUMNS (
            ALLSELECTED ( 'Table' ),
            "_Maxdate",
                MAXX (
                    FILTER (
                        ALLSELECTED ( 'Table' ),
                        [Parcel ID] = EARLIER ( 'Table'[Parcel ID] )
                            && [Courier] = EARLIER ( 'Table'[Courier] )
                    ),
                    [Effective Start Date]
                )
        ),
        [Effective Start Date] = [_Maxdate]
    )
RETURN
    SUMX ( FILTER ( _vtable, [Courier] = MAX ( 'Table'[Courier] ) ), [Mass] )

The result is as follow:

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[alee5210]

This is amazing! Exactly what I was after!

To expand on this, is there a way for me to take this farther. Let's say I have another column and I want to do a count on that column but again using only the latest records. Will I have to create another measure? Is there any way to achieve this in a simpler way, especially if I plan on making many different visuals? For example, I might want to do a count instead of a sum and I don't want to create a new measure each time since they would almost be the same.