Calculating Prev 2 Months Measures without Using DateAdd

[udian]

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!

[v-yuezhe-msft]

@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,
Lydia

[udian]

Hi 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!

[v-yuezhe-msft · Accepted solution]

@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

[udian]

Hi Lydia,

Sorry that i didn't understand your question...I see your point now.

i added the Day of Month filter in the report level filters while i had one of the visuals that wasn't filtered on a month level.

moving the Day of Month filter to the visual level filters solved the issue.

Thanks!