How get last previous date from variable date

[RafaelRSantosBR]

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

[EylesIT]

@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
        )

[RafaelRSantosBR]

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

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]

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

I will wait for responses, thanks

[sevenhills]

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

[RafaelRSantosBR]

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.

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

[sevenhills]

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

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