Supplies are limited. Contact info@espc.tech right away to save your spot before the conference sells out.
Get your discountScore big with last-minute savings on the final tickets to FabCon Vienna. Secure your discount
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.
Solved! Go to 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)
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)
User | Count |
---|---|
13 | |
12 | |
8 | |
8 | |
6 |
User | Count |
---|---|
28 | |
19 | |
13 | |
11 | |
7 |