Forum Discussion

alee5210's avatar
alee5210
Helper II
2 years ago
Solved

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 t...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
alee5210
Helper II
2 years ago

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.