datesadd(datesmtd) issue

[RMV]

Hi,

I have a calculated column to get the total amount in a month from last year from a transactional table, to be compared with the MTD figure in the current year in a visual.

The formula applied is

Amount MTD Previous Year. = CALCULATE(SUM('Transation'[Amount]),DATEADD(DATESMTD(DateCalendar[Date]),-12,MONTH))

And I have another table consists of calendar dates from several years period.

I use this calendar date table as an X-axis and filtering month and year.

The screen shot below is the visual I mentioned.

The result given from the previous year MTD from Jan-Nov is correct.

However, the Dec figure shows incorrect result. It's showing the total amount of Jan-Nov.

I need help to show me what is wrong with the formula & how to fix this, so I can get the correct figure to show MTD of each month from the previous year of the year that is selected as filter.

Thanks.

[Ashish_Mathur · Accepted solution]

Hi,

Try this measure (not a calculated column formula)

=CALCULATE(SUM('Transation'[Amount]),SAMEPERIODLASTYEAR(DateCalendar[Date]))

Does this work?

[RMV]

Hi @Ashish_Mathur,

I got the problem. It's because of the data.

Sorry to bother you.

Thanks anyway. I will keep in mind your suggestion next time.

[v-chuncz-msft]

@RMV,

To help close this thread, you may accept solution. Your contribution is highly appreciated.

[Ashish_Mathur]

You are welcome.