topic Re: MTD / QTD / YTD Dax not working for Last year calculations in DAX Commands and Tips

MTD / QTD / YTD Dax not working for Last year calculations

arniet — Wed, 24 Jun 2020 00:14:32 GMT

Hello,

I have a dataset that I am wanting to create the following metrics for:

Metrics:

========================

Total Sales
Year to Date
Month to Date
Year on year Sales
Month on Month Sales
Order Date To Ship Date Average Time

I have created measures for MTD / QTD and YTD for *this year* and it works fine. When I create it for *last year* I get a blank.

MTD Sales (This Year)($) = TOTALMTD(SUM(Sales_Fact[SalesAmount]),Sales_Fact[OrderDate])

MTD Sales (Last Year)($) = CALCULATE([MTD Sales (This Year)($)],DATEADD(Sales_Fact[OrderDate], -1, YEAR))

Am I using the correct date in my DAX? When do I use the date from the Date Dim table and when do I used the date from the Sales Fact table? This is where I might be going wrong. Please advise.

Please help as I have tried changing the format of the data, changed the data model etc.

Kind regards

Arnie.

Re: MTD / QTD / YTD Dax not working for Last year calculations

lbendlin — Wed, 24 Jun 2020 02:10:51 GMT

At the very minimum you need a calendar/dates table. you cannot use your facts table for date math.

Once the dates table is in place, have a look at the "quick measures" templates in Power BI. They include MTD and more. Also check https://daxpatterns.com

Re: MTD / QTD / YTD Dax not working for Last year calculations

arniet — Wed, 01 Jul 2020 01:50:29 GMT

Thanks for that, it works 🙂

kind regards