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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Tob_P
Helper IV
Helper IV

DATESMTD misbehaving if Sales in current month are zero

If Sales for Salesperson in current month = 0, the following DAX will then fetch the Sales Amount for the last month that it can see Sales (at least I think this is what is happening)

 

Sales MTD = CALCULATE([Amount Measure], DATESMTD('NAV_Sales History MASTER'[Posting Date]))
 
So in this example, Sales for Jan are £0 but it is returning Sales for December
 
SalesDELETE1.png
 
For such a simple piece of DAX I'm struggling firstly to understand why it would retrieve the incorrect figure, and then why it would start looking for something outwith the current month?
 
Totals for Salespeople who have sales in the current month are displaying fine.
 
Any ideas at all?
3 REPLIES 3
johnt75
Super User
Super User

I can't replicate that behaviour. Can you share a sample PBIX without any confidential information ?

Tob_P
Helper IV
Helper IV

Hi - Yes, all set up as such (changed Posting Date to my Date Table [Date] and same result. Otherwise, all is working fine with completely expected results across the board with the odd exception that if there are no sales in that month, then it will return the sales from the last month it can detect a sale in. There just seems no logic to why it would do that.

johnt75
Super User
Super User

For the time intelligence functions to work correctly you need to have a proper date table marked as a date table. Set up a date table linked to your sales table and then use the date column from that as the parameter to DATESMTD

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors