Using DAX to get the most recent date up to today's ("NOW()") date

[drew_darke]

I have this range of dates that are at the row level. Each date represents another field that was updated. It also has future dates as the fields have scheduled updates.

I would like to get the following measures:
Starting Value: This is easy because we can just use FIRSTDATE to grab that.
Current Value: This is where I am struggling. Using LASTDATE does not work because it will grab the highest date in the column (2023) but we want the LASTDATE from NOW() using the Now function as a ceiling for the lastdate function. The ideal value it would return is the 2021 row, because January 2021 is the most recent date.

Note: Using any YEAR calculations may not work with the rest of the data as some things change once a year like with this example, but also it could change every couple months.

[Greg_Deckler · Accepted solution]

@drew_darke Right, you have to use proper syntax for SELECTEDCOLUMNS, I just used ... because I didn't know your columns and am lazy. So, something like:

Current Value = CALCULATE(MAX(Table[Value]), LASTDATE((SELECTCOLUMNS(FILTER(Table, Table[Date] <= NOW()),"Date",[Date])))

[parry2k · Accepted solution]

@drew_darke or something like this

Last date measure = 
CALCULATE ( MAX ( Table[Date] ), ALLSELECTED ( Table ), Table[Date] <= NOW() ) )

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[v-rzhou-msft · Accepted solution]

Hi @drew_darke 

Try this measure.

Measure = CALCULATE(MAX('Table'[Value]), LASTDATE(CALCULATETABLE(VALUES('Table'[Date]),FILTER('Table','Table'[Date]<NOW()))))

If this measure still couldn't help you solve your problem, please share a sample with me without sensitive data by your Onedrive for Business.

Best Regards,
Rico Zhou

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

[parry2k · Accepted solution]

@drew_darke or something like this

Last date measure = 
CALCULATE ( MAX ( Table[Date] ), ALLSELECTED ( Table ), Table[Date] <= NOW() ) )

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Greg_Deckler]

@drew_darke Can you not just use LASTDATE(SELECTCOLUMNS(FILTER('Table',[Date]<NOW())...))

[drew_darke]

@Greg_Deckler 

Current Value = CALCULATE(MAX(Table[Value]), LASTDATE((SELECTCOLUMNS(FILTER(Table, Table[Date] <= NOW())))))

I am receiving an error saying SELECTCOLUMNS function needs 2 arguments.

Also I want to clear up that I am not after the date itself, but the value in another column the date is keyed for.

[Greg_Deckler · Accepted solution]

@drew_darke Right, you have to use proper syntax for SELECTEDCOLUMNS, I just used ... because I didn't know your columns and am lazy. So, something like:

Current Value = CALCULATE(MAX(Table[Value]), LASTDATE((SELECTCOLUMNS(FILTER(Table, Table[Date] <= NOW()),"Date",[Date])))

[jeffharrison]

@Greg_Deckler  Hello - Please forgive me if I'm not using correct forum etiquette by replying to a closed thread, but this is the closest answer I've found. 
I'm getting error "Function 'LASTDATE' cannot be used with columns added by ADDCOLUMNS or SUMMARIZE functions".  This is the measure I'm using

Current Balance = 
    CALCULATE(
        SUM('Deposits'[CurrentBalance])
        ,LASTDATE(
            SELECTCOLUMNS(
                FILTER('Date','Date'[Calendar Date]<NOW())
                ,"Date",'Date'[Calendar Date]
            )
        )
    )

My date table has future dates in it and it sounded like this was the closest solution, but I can't figure out what I'm doing wrong.  Any assistance would be greatly appreciated!

[Greg_Deckler]

Try:

Current Balance = 
  VAR __LastDate = 
    MAXX(
      FILTER('Date', [Calendar Date]<NOW()),
      [Calendar Date]
    )
  VAR __Result = SUMX(FILTER('Deposits', [Date] = __LastDate), [CurrentBalance])
RETURN
  __Result

[MJAGUSIAK]

@Greg_Deckler
I am trying to do the same on my PBI. 
here are my column names and I need to find the last status update. I created a new column that combines the last event day and time. 

Customer Confirmation Number

Delivery Confirmation Number

Manifest Date

Receive Date

Last Event Date

Last Event Time

Last Event Description

Zip or Postal Code

Please let me know how I should go about getting this most recent status. 

I tried to use the following but its not pulling back the latest status:

Last Status =

VAR CustomerNumber = 'DHL Status'[Delivery Confirmation Number]

RETURN

maxx(FILTER(ALL('DHL Status'), 'DHL Status'[Delivery Confirmation Number] = CustomerNumber),

    'DHL Status'[Last Event Description])

[jeffharrison]

That worked!! Thanks so much 🙂