MTD to match the period last year

[IrishChristof]

Hi, I'm trying to devise a method whereby I can compare MTD versus the same period last year.. but it needs to be the same period in terms of days.. 

eg: today is the 13th September, MTD would be sales made from Sunday 1st September to Friday 13th September.

but as we have slower trading days on a Sunday, i need to compare against the same period last year which would be Sunday 2nd September to Friday 14th September.

My MTD formula works a treat

=TOTALMTD ( SUM (BookingTable[Total Revenue] ),CallDate[Date] )

But when i attempted a SAMEPERIODLASTYEAR formula, it was doing Saturday 1st September to Thursday 13th September

=CALCULATE ( [NetRevenueMTD], SAMEPERIODLASTYEAR (CallDate[Date]))

I thought i could use the DATEADD formula to move everything on one day, but that didnt work and instead gave me Saturday 1st September to Friday 14th September!

=CALCULATE ( [NetRevenueMTD], SAMEPERIODLASTYEAR (DATEADD(CallDate[Date] ,+1,DAY )))

If it helps, I have a field on my "CallDate" table that shows the equivalent date of last year.

So not sure if this can be used to any effect.

Any help would be great.

Many thanks

Chris

[amitchandak]

Can you try something like this 

Year 364 = 
Var _end_date= max('Date'[Date Filer])-364
Var  _start_date =min('Date'[Date Filer])-364

Var _last_year_val= CALCULATE(sum(Sales[Sales Amount]),Sales[Sales Date] >= _start_date && (Sales[Sales Date]) <= _end_date)
return
_last_year_val

Date here is marked at the date in power bi

[IrishChristof]

Thanks for this.. 

I applied it into my model in the following way...

=Var _end_date= MAX('CallDate'[Date])-364
Var  _start_date =MIN('CallDate'[Date])-364

Var _last_year_val= TOTALMTD ( SUM (BookingTable[Total Revenue] ),CallDate[Date] >= _start_date && (CallDate[Date]) <= _end_date)
return
_last_year_val

but it still doesnt give me what i need, it seems to be giving me a MTD still with the 1st day of the month last year included.

All being well, the two yellow rows should reconcile.. but as you can see they do, with the exception of day1.

 (*Rec - is the difference in Total MTD Revenue for Last Year compared to previous day)

It only seems to be the £115,397 that's the problem, as the other MTD total is incrimenting by the amount I would expect.

Hope that all makes sense.

All being well, the NetRevenueMTDLY figure would match the RevenueLast7DaysLY figure on the 7th September.

[amitchandak]

For me, your initial formula also working. Just check the date filter at dim or fact

last year day = CALCULATE(min(Sales[Sales Date]),SAMEPERIODLASTYEAR(DATEADD('Date'[Date Filer],-1,DAY)))
Last Year Same Week Day Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR(DATEADD('Date'[Date Filer],-1,DAY)))

See data comparison

[IrishChristof]

I am confused by this, surely Friday 28th June 2019 equivalent date is Friday 29th June 2018, and therefore the 'Last Year Same Week Day Sales' should be 407 and not 118.

I got Last Year day sales working fine, i just wanted to know if there is a way i can have MTD Sales.

So when I refresh the report on Monday, i have a cell saying MTD sales in X, and the same period last year was Y (the same period being Sun 2nd Sept to Sun 16th Sept - NOT 1st to 15th)

[amitchandak · Accepted solution]

Actually in the second set of formula, I should have added one not subtracted 

MTD.

Year 364 MTD = 
Var _end_date= max('Date'[Date Filer])-364
Var  _start_date =minx(ALLSELECTED('Date'),STARTOFMONTH('Date'[Date Filer]))-364

Var _last_year_mtd_val= CALCULATE(sum(Sales[Sales Amount]),Sales[Sales Date] >= _start_date && (Sales[Sales Date]) <= _end_date)
return
_last_year_mtd_val

To Test , You can return _start_date & " and " & _end_date and check dates

[IrishChristof]

Superb, thank you so much!!

[parry2k]

@IrishChristof I had solution posted here for the similar question