Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Most recent date measure

I am trying to calculate thhe most recent product level by date and user, but i am looking to show this as a chart that counts the number of products IDs  by product level. But will need to be by the max date as i only want to look at the latest date below is some sample data i am working with;

 

IdUsernameProduct IDProduct LevelDate

1John1306/02/2020
2John1107/02/2020
3John2325/02/2020
4Steve3118/02/2020
5Steve3310/02/2020
6Steve4107/02/2020
7Steve4306/02/2020
8Steve4004/02/2020
9Steve5324/02/2020
10Steve6107/02/2020
Need Help
Show and Tell
  • Icey's avatar
    Icey
    6 years ago

    Hi Anonymous ,

     

    Try this:

    Most Recent Date by User = 
CALCULATE (
    MAX ( 'Table'[Date] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[Username] = MAX ( 'Table'[Username] )
    )
)
    count = 
IF (
    [Most Recent Date by User] = MAX ( 'Table'[Date] ),
    COUNTROWS (
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Username] = MAX ( 'Table'[Username] )
                && 'Table'[Product Level] = MAX ( 'Table'[Product Level] )
        )
    )
)

     

    Best regards

    Icey

     

    If this post helps, then consider Accepting it as the solution to help other members find it more quickly.

5 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Anonymous , can you share expected output in table format.

     

    This can work

    lastnonblankvalue(table[LevelDate],max(Table[Product Leve]))

     

    But I think you said this will not solve the purpose on the last reply on last topic

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      amitchandak No it wont work, i need the table to do a count of product Ids that sit per level. But i only want to look at the max date per product ID

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Anonymous , try like

    countx(filter(summarize(Table,table[user], table[product],table[Product Level],"_1", calculate(lastnonblankvalue(table[LevelDate],max(Table[Product Leve])),allexcept(Table,table[user], table[product]))) ,[_1] =[Product Level]),[product])

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      amitchandak its still not working i can do it statically by taking the max date then saying if date = max date then take level but i need to be able to do this dynamically to change when a date is filtered

  • Icey's avatar
    Icey
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

     

    Try this:

    Most Recent Date by User = 
CALCULATE (
    MAX ( 'Table'[Date] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[Username] = MAX ( 'Table'[Username] )
    )
)
    count = 
IF (
    [Most Recent Date by User] = MAX ( 'Table'[Date] ),
    COUNTROWS (
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Username] = MAX ( 'Table'[Username] )
                && 'Table'[Product Level] = MAX ( 'Table'[Product Level] )
        )
    )
)

     

    Best regards

    Icey

     

    If this post helps, then consider Accepting it as the solution to help other members find it more quickly.