DAX to find LastDate of Previous Month

[Vijay08V]

Hi,

I need help in writing a DAX function to find the last date of previous month from Date Column based on the Date filter selection. For example If I select 23-08-22 date, function should be able to throw output 29-07-22 as last date of previous month. Below is the sample date column data. 

Date
18-07-2022
19-07-2022
20-07-2022
21-07-2022
22-07-2022
25-07-2022
26-07-2022
27-07-2022
28-07-2022
29-07-2022
01-08-2022
02-08-2022
03-08-2022
04-08-2022
05-08-2022
08-08-2022
09-08-2022
10-08-2022
11-08-2022
12-08-2022
15-08-2022
16-08-2022
17-08-2022
18-08-2022
19-08-2022
22-08-2022
23-08-2022

[jgeddes · Accepted solution]

You can try:

Last Date In Previous Month =

var _selectedDate =

SELECTEDVALUE(dates_List[Date])

var _previousMonth =

month(EOMONTH(_selectedDate,-1))

Return

CALCULATE(

    MAX(dates_List[Date]),

    MONTH(dates_List[Date]) = _previousMonth

)

[Vijay08V]

It worked! Thank you.

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

It is for creating a measure.

Expected output measure: =
VAR _previousmonthenddate =
    EOMONTH ( MAX ( Data[Date] ), -1 )
VAR _result =
    MAXX (
        FILTER ( ALL ( Data[Date] ), Data[Date] <= _previousmonthenddate ),
        Data[Date]
    )
RETURN
    _result

[jgeddes · Accepted solution]

You can try:

Last Date In Previous Month =

var _selectedDate =

SELECTEDVALUE(dates_List[Date])

var _previousMonth =

month(EOMONTH(_selectedDate,-1))

Return

CALCULATE(

    MAX(dates_List[Date]),

    MONTH(dates_List[Date]) = _previousMonth

)