Forum Discussion
Calculating Prev 2 Months Measures without Using DateAdd
Hi,
I have a tbular model and in many cases i need to create prev 2 months measures such as Volume_Amount_Prev_2_Months (2 months ago).
i want to build this measure without using DateAdd function as i want the users to be able to filter on DayOfMonth causing an error when using DateAdd.
how can i create it?
Thanks!
udian,
As my previous post, when you select single day in the slicer, what year and what month do you refer to? In other words, which is the baseline of the previous 2 months measure when you make selections in the slicer?
In your scenario, you should also add a year/month slicer to make a vaild date so that the measure returns previous 2 month value based on this date.
Regards,
Lydia
4 Replies
udian,
What error message do you get? When you select single day in the slicer, what year and what month do you refer to? In other words, which is the baseline of the previous 2 months measure when you make selections in the slicer?
Please share sample data and post expected result here.
Regards,
LydiaHi Lydia,
I have the following measures:
Volume Amount USD - Prev Month:=CALCULATE(SUM([Volume_Amount_USD]),PREVIOUSMONTH(Dim_TimeTable_EST[Date EST]))
Volume Amount USD - 2 Months Ago:=CALCULATE([Volume Amount USD - Prev Month],DATEADD(Dim_TimeTable_EST[Date EST],-1,MONTH))
The error message that i get when i try to filter on day of month (from the Dim_TimeTable_EST) is:
Function 'DATEADD' only works with contiguous date selections.
Thanks!
udian,
As my previous post, when you select single day in the slicer, what year and what month do you refer to? In other words, which is the baseline of the previous 2 months measure when you make selections in the slicer?
In your scenario, you should also add a year/month slicer to make a vaild date so that the measure returns previous 2 month value based on this date.
Regards,
Lydia
