Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
@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.
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.
I make this DAX, but i think have most elegant way to do.
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.
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
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
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
25 | |
10 | |
7 | |
6 | |
6 |
User | Count |
---|---|
30 | |
11 | |
11 | |
10 | |
6 |