Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
alee5210
Helper II
Helper II

Make Chart Using Latest ID Based On User Selected Date

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.

alee5210_0-1718171570650.png

 

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.

alee5210_3-1718175298789.png

 

 

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.

alee5210_4-1718175527457.png

 

(Using ths show measure)

alee5210_5-1718175624688.png

 

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

 

 

 

 

 

 

 

 

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

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:

vzhengdxumsft_0-1718247184502.png

 

 

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.

View solution in original post

2 REPLIES 2
v-zhengdxu-msft
Community Support
Community Support

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:

vzhengdxumsft_0-1718247184502.png

 

 

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.

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.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.