MTD & MTD LY Calculation

DAX DAX Calculations
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

2018-08-15%2011_57_51-Part%20Sales%20-%20Power%20BI%20Desktop
2018-08-15 11_57_51-Part Sales - Power BI Desktop.png484×669 24.5 KB

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.

7

Hi
If I try to replace the dates with product group it stops working. How do I fix that?

Lee