cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Frequent Visitor

## 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?

1 ACCEPTED SOLUTION
Community Support

Hi @the_sin ,

``````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])
)``````

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 3
Community Support

Hi @the_sin ,

``````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])
)``````

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.

Super User

Resolver I

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-D...

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.

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors