MTD Comparison to same period last year

[Johanna]

I have sales data from 1st of Jan 2016 until 4th of June 2017.

I would like compare the total sales of first 4 days in June 2017 to same period in 2016, but I always get as comparison the whole month of June from 2016. It should also run as MTD comparison, when I update more sales data for June 2017.

Is there a way to compare only those 4 days from each year?

[v-sihou-msft · Accepted solution]

@Johanna

You can just create a MTD LY measure and use SAMEPERIODLASTYEAR() in CALCULATE().

MTD LY = CALCULATE(TOTALMTD(SUM('Calendar'[Amount]),'Calendar'[Date]),SAMEPERIODLASTYEAR('Calendar'[Date])) 

Regards,

[raghavnetenrich]

I used DX formula as 

Previous period = CALCULATE(SUM(Sheet1[Extended]), SAMEPERIODLASTYEAR(Sheet1[Rev Rec Month]), ALL(Sheet1))

But it is showing up only for year breakup but when i try doing it for client wise...same value is getting refelcted for other clients as well

 

I want the exact preious year same period revenue over der. please advice.......

[AliciaS]

I´m also trying to get the same anwer using the formula, but no luck so far.

MTD LY = CALCULATE(TOTALMTD(SUM('Calendar'[Amount]),'Calendar'[Date]),SAMEPERIODLASTYEAR('Calendar'[Date])) 

 VENTAS MTD = CALCULATE(TOTALMTD(SUM(RUADETALLE[CANTIDAD]);Calendario[Date];SAMEPERIODLASTYEAR(Calendario[Date])))

I get empty cells.

Hope someone can help me.

Thank you,

[VijayReddy]

Hi,

Below formula worked for me.

Output is Revenue for same period last year calculated until same date of current period.

Revenue SPLY MTD = TOTALMTD(SUM('FactTable'[Revenue]), DATEADD(FILTER(DATESMTD('Calendar'[DateID]), 'Calendar'[DateID]<TODAY()), -1,YEAR))

[Anonymous]

Hi, How does this formula work when the date column has multiple time values in it? How can we exclude duplicates caused by the time factor?

[jdbuchanan71]

The formula works against the calendar table which won't have duplicate dates in the date column.

[DeepDive]

This is not working when I used it, how to calculate at prodcut level then sku level ..then MTD then MTD last year( MTD = 1 to 12 July for current and for last year).. any suggestion..

It's working fine when i am using Datesbetween using static dates but i need to convert it dynamically

---

@VijayReddy wrote:

Hi,

Below formula worked for me.

Output is Revenue for same period last year calculated until same date of current period.

Revenue SPLY MTD = TOTALMTD(SUM('FactTable'[Revenue]), DATEADD(FILTER(DATESMTD('Calendar'[DateID]), 'Calendar'[DateID]<TODAY()), -1,YEAR))

---

[jdbuchanan71]

This article will walk you through how to fix the problem you are having.

https://www.sqlbi.com/articles/hiding-future-dates-for-calculations-in-dax/

It shows how to use filtering on a special column you add to your date table to stop calculations from going past the point where you have current data.

PY MTD Limited = 
CALCULATE(
    [MTD amount],
    CALCULATETABLE(
        SAMEPERIODLASTYEAR(Dates[Date]),
        Dates[Is Past] = TRUE
    )
)

[raghavnetenrich]

I used DX formula as 

Previous period = CALCULATE(SUM(Sheet1[Extended]), SAMEPERIODLASTYEAR(Sheet1[Rev Rec Month]), ALL(Sheet1))

But it is showing up only for year breakup but when i try doing it for client wise...same value is getting refelcted for other clients as well

 

I want the exact preious year same period revenue over der. please advice.

[VijayReddy]

I also used this code and it is not returning any values for last year MTD. I am sure we do have data for last year. However, no errors in the formula.  

[sokg]

On your date table you must create the following measures:

BeginCurrentYear = DATE(YEAR(TODAY());1;1)

BeginPreviousYear = DATE(YEAR(TODAY())-1;1;1)

SameDay = TODAY()

SameDayPreviousYear = DATE(YEAR(dCalendar[SameDay])-1;MONTH(dCalendar[SameDay]);DAY(dCalendar[SameDay]))

and then, this calculated column

CurrentPreviousYearToDate = IF(AND(dCalendar[BeginCurrentYear]<=dCalendar[Date];dCalendar[Date]<=dCalendar[SameDay]);"1";IF(AND(dCalendar[BeginPreviousYear]<=dCalendar[Date];dCalendar[Date]<=dCalendar[SameDayPreviousYear]);"0"))

so your measures for MTD are

Sales MTD = CALCULATE(TOTALMTD([Sales];DATESMTD(dCalendar[Date]));dCalendar[Month No]=MONTH(TODAY());dCalendar[Year]=YEAR(TODAY()))

and

Sales PYMTD = CALCULATE([Sales];DATEADD(DATESMTD(dCalendar[Date]);-1;YEAR);dCalendar[Month No]=MONTH(TODAY());dCalendar[Year]=YEAR(TODAY())-1;dCalendar[CurrentPreviousYearToDate]="0")

[Anonymous]

This worked perfectly, it is just what i was looking for!

Thank you! 

[Ylli]

Thank you for the code about MTD Comparison. It is very helpfull.

I am testing your code, and evrything looks fine, but i am not getting the sales for PYMTD.

Thanks