Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

How to get the latest time for each date?

I have a calculated table that looks something like this 

 

Forex Currency Position & PnL = 
SUMMARIZE (
    FILTER (
        AccountData,
        (AccountData[Name] = "StkVal" || AccountData[Name] = "OptVal" || AccountData[Name] = "WarVal" || AccountData[Name] = "Cash") &&
        (AccountData[Account] = "U8061749" || AccountData[Account] = "U7657172" || AccountData[Account] = "U11134039") &&
        AccountData[Cur] = "USD"
    ),
    AccountData[Account],
    AccountData[Datetime],
    "FX Poz", SUM(AccountData[Value])
)

 

 


I need to use the latest time for each account number, how can I filter the dates so that I only use the latest time for each date? I have a lot of non-continuous dates in that table as well, so for each date in that table, I need to use the latest time found. How can I edit that DAX formula so that it shows only the latest time's value?

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Anonymous ,

    Please have a try.

    Forex Currency Position & PnL = 
SUMMARIZE (
    FILTER (
        AccountData,
        (AccountData[Name] = "StkVal" || AccountData[Name] = "OptVal" || AccountData[Name] = "WarVal" || AccountData[Name] = "Cash") &&
        (AccountData[Account] = "U8061749" || AccountData[Account] = "U7657172" || AccountData[Account] = "U11134039") &&
        AccountData[Cur] = "USD"
    ),
    AccountData[Account],
    MAXX(FILTER(AccountData, AccountData[Account] = EARLIER(AccountData[Account])), AccountData[Datetime]),
    "FX Poz", SUM(AccountData[Value])
)

     

     

    How to Get Your Question Answered Quickly 

     

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

     

    Best Regards
    Community Support Team _ Rongtie

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

     

     

3 Replies

  • cpearson's avatar
    cpearson
    Icon for Resolver I rankResolver I
    3 years ago

    Have a read through this post for a good way to do it in Power Query: https://community.fabric.microsoft.com/t5/Desktop/How-to-add-Row-number-over-partition-by-Customer-DAX-or-M-query/m-p/566949

     

    If that doesn't work for you and you need to use DAX then read through this: https://learn.microsoft.com/en-us/dax/rownumber-function-dax

     

    You need to partition by your account number and order by your date in descending order. You then have the relevant records for max date/time where the row number output = 1.

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Anonymous ,

    Please have a try.

    Forex Currency Position & PnL = 
SUMMARIZE (
    FILTER (
        AccountData,
        (AccountData[Name] = "StkVal" || AccountData[Name] = "OptVal" || AccountData[Name] = "WarVal" || AccountData[Name] = "Cash") &&
        (AccountData[Account] = "U8061749" || AccountData[Account] = "U7657172" || AccountData[Account] = "U11134039") &&
        AccountData[Cur] = "USD"
    ),
    AccountData[Account],
    MAXX(FILTER(AccountData, AccountData[Account] = EARLIER(AccountData[Account])), AccountData[Datetime]),
    "FX Poz", SUM(AccountData[Value])
)

     

     

    How to Get Your Question Answered Quickly 

     

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

     

    Best Regards
    Community Support Team _ Rongtie

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