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

[arniet]

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.

[arniet]

Thanks for that,  it works 🙂

kind regards

[lbendlin · Accepted solution]

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