MTD LY Same time period

[Heuers]

Hi,

Im trying to compare the MTD of the current period with the relevant MTD of the period LY. The issue I am experiencing is the period LY has a full years worth of data and thus trying to perform a LFL comparison in the month you are in is not accurate. In order to get around this I have performed the below:

MTD TY formula:

GB_MTD:=TOTALMTD([GB],Cal[Date])

MTD LY: GB_MTD_LY2:=IF(LASTDATE(Cal[Date])>TODAY()-1,BLANK(),
CALCULATE([GB_MTD],DATEADD(Cal[Date],-1,YEAR)))

After completing this I have had the following issues. When looking at it from a days POV everything looks fine: the LY formula cuts it off as I expect at todays date, however - once I look at it monthly I lose the data for the month that is not completed.

I have made sure the calendar table has all the relavant dates for the month concerned - is this something to do with using datesadd for months that are not completed?

I am new to the forum and can post examples of the issue I am having - just not sure how to do so.

Any advice wiould be GREATLY appreciated for a new to the forum and pretty new to PBI user.

Thanks

Shane 

[v-frfei-msft · Accepted solution]

Hi @Heuers ,

I have created a sample for your reference.

GB_MTD = CALCULATE(SUM('Table'[value]),DATESMTD('date'[Date]))

LY = TOTALMTD(CALCULATE(SUM('Table'[value]),SAMEPERIODLASTYEAR('date'[Date])),'date'[Date])

Pbix as attached.

[amitchandak]

@Heuers , try like

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))

last year MTD Sales =
var _max = date(year(today())-1,month(today()),day(today()))
return
CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)),'Date'[Date]<=_max)

In place of Today, You can use Max date of you fact

[Heuers]

@amitchandak 

Thanks for the reply - tried your solution below and its works great for comparing the same LFL period.

I can now perform MTD vs MTD LY calculation.

Could you please provide me with a reason why the following happens: I have a pivot connected to that model: If a blank out from a certain day as in my orginal soloution, the days blank out correctly, why does the month total also become blank whilst there is data for the corresponding days under it? 

Measure 1 is your solution blanking out from 27/04 onwards. LY measure is your solution with no blank.

Thanks