topic Re: How get last previous date from variable date in DAX Commands and Tips

How get last previous date from variable date

RafaelRSantosBR — Wed, 25 Oct 2023 19:20:26 GMT

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

Re: How get last previous date from variable date

RafaelRSantosBR — Wed, 25 Oct 2023 19:43:14 GMT

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

I will wait for responses, thanks

Re: How get last previous date from variable date

sevenhills — Wed, 25 Oct 2023 20:53:52 GMT

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

Re: How get last previous date from variable date

EylesIT — Wed, 25 Oct 2023 22:10:07 GMT

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 )

Re: How get last previous date from variable date

RafaelRSantosBR — Thu, 26 Oct 2023 15:33:43 GMT

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.

Re: How get last previous date from variable date

RafaelRSantosBR — Thu, 26 Oct 2023 15:42:36 GMT

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

Re: How get last previous date from variable date

sevenhills — Thu, 26 Oct 2023 22:38:03 GMT

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