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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
RafaelRSantosBR
Regular Visitor

How get last previous date from variable date

Hi, I have a Fact table linked to DimDate.

I Need get last date previous month from date passed as measure.

eg.

Measure:

LastDate = 2023-10-25

 

Dax

Var lastDate = LastDate

Var vYear = Year(LastDate)

Var vMonth = Month(LastDate)

Var firstdate = Date(vYear, vMonth, 1)

 

The last day of previous month of my fact table is 2023-09-29

 

I need a DAX to return 2023-09-29  instead of 2023-09-30(last day of previous month of DimDate table)

 

Thanks

6 REPLIES 6
EylesIT
Resolver II
Resolver II

@RafaelRSantosBR, here is my suggested solution.

Create a measure with this DAX expression. I think you need to query the FactTable, but remove the filters from both the FactTable and the dimDate table.

 

Last Date in Previous Month from Fact Table = 
    VAR vLastDate = [LastDate]
    VAR vPrevMonth = EDATE(vLastDate, -1)
    VAR vYear = YEAR(vPrevMonth)
    VAR vMonth = MONTH(vPrevMonth)

    RETURN 
        CALCULATE(
            MAX(FactTable[Date]),
            ALL(FactTable),
            ALL(dimDate),
            YEAR(FactTable[Date]) = vYear,
            MONTH(FactTable[Date]) = vMonth
        )

 

 

 

@EylesIT your calculation return the last day of previous month, but not the last day of previous day that exists in fact table.

RafaelRSantosBR_0-1698334094149.png

I made some updates in your DAX, because the fact table doesn't have date value, just the foreign key of date value. Follows the code:

Last Date in Previous Month from Fact Table = 
    VAR vLastDate = [UltimaData]
    VAR vPrevMonth = EDATE(vLastDate, -1)
    VAR vYear = YEAR(vPrevMonth)
    VAR vMonth = MONTH(vPrevMonth)

    RETURN 
        CALCULATE(
            MAX('Publico DimData'[Data]),
            ALL('Investimento FatoEvolucaoFundo'),
            ALL('Publico DimData'),
            YEAR('Publico DimData'[Data]) = vYear,
            MONTH('Publico DimData'[Data]) = vMonth
        )

Follows the model.

RafaelRSantosBR_1-1698334408435.png

 

RafaelRSantosBR
Regular Visitor

I make this DAX, but i think have most elegant way to do.

RafaelRSantosBR_0-1698262961489.png


I will wait for responses, thanks

Just curious, did you try using EOMONTH and -1.

I cannot read spanish else would have refined your DAX.

 

measure1 =
VAR _PrevMonthEndDate = EOMONTH ( LastDate, -1 )
VAR _resultLastTxDate =
    MAXX (
        FILTER ( ALL ( 'Table1'[Date] ), 'Table1'[Date] <= _PrevMonthEndDate),
        'Table1'[Date]
    )
RETURN    _resultLastTxDate

 

Hi @sevenhills , thanks for reply.

Your DAX return the last day of previous month of Dimension Date Table, and not the last day of previous month of Fact Table.

RafaelRSantosBR_3-1698334886197.png

 

I made this updates to work:

measure1 = 
VAR _PrevMonthEndDate = EOMONTH ( [UltimaData], -1 )
VAR _resultLastTxDate =
    MAXX (
        FILTER ( ALL ( 'Publico DimData'[Data] ), 'Publico DimData'[Data] <= _PrevMonthEndDate),
        'Publico DimData'[Data]
    )
RETURN
_resultLastTxDate

Thanks for help

I was not sure whether it worked or not...

 

Now, I see what you want is previous month max transaction date based on certain colum. I will recommend to try this.

 

Below is the DAX from Adventure Works DW 2020

Prev.Month - Max.Sales.Tx.Date - Sales Amount = 
var _PrevMonthStartDate  = EOMONTH ( [Last Sales Tx Date], -2 ) + 1 
var _PrevMonthEndDate    = EOMONTH ( [Last Sales Tx Date], -1 )
var _LastDateInPreviousMonthTx =  LASTNONBLANK ( 
       Filter( all('Date'[Date]), 
            'Date'[Date] >= _PrevMonthStartDate 
            && 'Date'[Date] <= _PrevMonthEndDate)
       , CALCULATE ( SUM ( Sales[Sales Amount] ) )) 
return _LastDateInPreviousMonthTx

 

sevenhills_1-1698359799818.png

 

Similarly, You can do like this by adjusting the fact table column and dim date column.

 

measure1 = 
var _PrevMonthStartDate  = EOMONTH ( [UltimaData], -2 ) + 1 
var _PrevMonthEndDate    = EOMONTH ( [UltimaData], -1 )
var _LastDateInPreviousMonthTx =  LASTNONBLANK ( 
        Filter( all('Dim Date'[Date]),
             'Dim Date'[Date] >= _PrevMonthStartDate 
             && 'Dim Date'[Date] <= _PrevMonthEndDate)
        , CALCULATE ( SUM ( FactTable[Sales Amount] ) )) 
return _LastDateInPreviousMonthTx

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.