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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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