Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Vijay08V
Helper III
Helper III

DAX to find LastDate of Previous Month

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
2 ACCEPTED SOLUTIONS
jgeddes
Super User
Super User

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
)
 




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





View solution in original post

Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

It is for creating a measure.

 

Picture1.png

 

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

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

View solution in original post

3 REPLIES 3
Vijay08V
Helper III
Helper III

It worked! Thank you.

Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

It is for creating a measure.

 

Picture1.png

 

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

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

jgeddes
Super User
Super User

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
)
 




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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