Help with DATEADD

[alexggrantham]

Hello,

I have a date table joined to a fact table and I'm counting rows in the fact table.

The ultimate goal is to be able to count the rows in the fact table where the date is before the Max Date, which is the most recent date for which there is data (19/11/2024). I also want to be able to do the same for last year i.e. count rows where the date is prior to 19/11/2023.

To do this I need to calculate the most recent date for which there is data and I have achieved this with the following code:

However, as you can see, the Max Date with DATEADD is not returning 19/11/2023 as I wanted it to. It is returning the max date per month.

How can the Max Date measure be written in such a way that when used in a DATEADD function, it returns the same date a year ago, for all rows?

Thank you.

[Rupak_bi · Accepted solution]

Try This

Max date dateadd = calculate([Max Date],dateadd(date[Max Date],-1,Year))
alternate
Max date dateadd =

var _day = day([Max Date])
Var _month = Month([Max Date])
Var _Year_P = Year([Max Date])-1
Return
date(_year_P,_month,_day)