MTD & MTD LY Calculation


#1

So MTD is pretty basic and is working ok.

With my LY calculation i want to calculate up to the same day this year, even though i’m looking at the whole month of August. ie, it’s the 14th Today, i want to know what sales were up to the 14th August Last Year, the calc i’m using is giving me the whole month.

Invoice Amount MTD LY = TOTALMTD ( [Invoice Amount] , SAMEPERIODLASTYEAR ( ‘Date’[Date] ) )

One solution i’ve seen is to reduce the calendar to only include days up until todays date, and this does work, but there has to be a better solution.

Any ideas?


#2

Hi Benaud,

Have you seen this one?

I think this should get you the answer.

Let me know if not. Thanks


#3

Brilliant… Thanks


#4

Ok, So the solution in the video works when viewing the data by day, such as in the chart.

I wanted to use a series of cards to show the sales to date for the month vs the same day of the month last year and work out a percentage vs last year MTD.

I added 2 columns to my date table view from the DW.
Month2: contains the month name & year (Sep 2018), except for the Current Month which contains the text “Current Month”
DayDescr: Blank except for values for “Today” and “Yesterday”

Invoice Amount MTD LY = 
IF (SELECTEDVALUE('Date'[Month2]) = "Current Month",
    CALCULATE( 
        [Invoice Amount Same Mth LY], 
        FILTER('Date', 'Date'[DayDescr] = "Yesterday")
    ),
    [Invoice Amount Same Mth LY]
)

Invoice Amount Same Mth LY = TOTALMTD([Invoice Amount],DATEADD(‘Date’[Date],-1,YEAR))

I did the same calc for YTD too


#5

Cool, is it actually working for you now? I was just about to start working on an example but actually looks like you might have it?

Let me know.
Thanks


#6

got it working… thanks. probably better ways to do it, but i am a good hacker.