Power BI: how to select all columns where date is greater in Power BI

0
votes

I'm a newbie I have just started using power bi. I have a table 'users' with a column name 'Date' and some other columns. I want to select the latest record where Date is largest. I don't know how to accomplish this with dax or power query. Really appreciate any sort of help.

Table

 ID       AddedDate          Value    
 1       2018/11/01 00:00      105  
 2       2018/11/02 00:00      101  
 3       2018/11/03 00:00      101

Output

ID       AddedDate          Value
 3       2018/11/03 00:00      101
1
powerbipowerquery
What do you mean by select the latest record where Date is largest ? Please provide some sample data and desired output. – Petaflop
@Petaflop I have updated the post – Andrain

1 Answers

2
votes

DAX measure formula (make sure it's a Measure, not a calculated column):

Latest Value =
VAR 
   Latest_Date = CALCULATE ( MAX ( Users[AddedDate] ), ALL ( Users ) )
RETURN 
   CALCULATE ( SUM ( Users[Value] ), Users[AddedDate] = Latest_Date )

Result:

enter image description here

How it works:

  1. First, we find the latest date by looking for a max date in the entire (unfiltered) table, and save it in a variable.
  2. Second, we tell DAX to sum only values where date is equal to the latest date we have saved.