Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
arniet
Frequent Visitor

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

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.
 
Capture1.PNG

Capture2.PNG

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

 

Kind regards

Arnie.

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

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

View solution in original post

2 REPLIES 2
arniet
Frequent Visitor

Thanks for that,  it works 🙂

 

kind regards

lbendlin
Super User
Super User

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.