Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
I'm using the following formulas for YTD and YTD prior year:
Total EV Sales YTD = CALCULATE('Payment_Detail_Daily'[Total EV Sales], DATESYTD('Payment_Detail_Daily'[Date]))
Total EV Sales YTD Last Year = CALCULATE('Payment_Detail_Daily'[Total EV Sales YTD], SAMEPERIODLASTYEAR('Payment_Detail_Daily'[Date]))
I have a field named "location" and would like to filter the YTD and YTD prior sales by locations. However, once I apply the filter it gives me the following error:
Can't display visual
Error Message: MdxScript(Model) (5, 115) Calculation error in measure 'Payment_Detail_Daily'[Total EV Sales YTD Last Year]: Function 'SAMEPERIODLASTYEAR' expects a contiguous selection when the date column is not unique, has gaps or it contains time portion.
Any suggestions would be appreciated thanks!
Solved! Go to Solution.
Your Measures need to reference the Date column in your Calendar Table
Total EV Sales YTD = CALCULATE ( 'Payment_Detail_Daily'[Total EV Sales], DATESYTD ( 'CalendarTable'[Date] ) ) Total EV Sales YTD Last Year = CALCULATE ( [Total EV Sales YTD], SAMEPERIODLASTYEAR ( 'CalendarTable'[Date] ) )
With this change your Measures should work even after you apply slicers/filters
Good Luck!
BTW Always add a table name before a column name, and never add a table name before a measure name
EDIT: Here's a demo with 2 Gauges and a Slicer.
The first Gauge uses Measures that reference the 'CalendarTable'[Date] and the second does not!
CALENDAR ( "2013,01,01", "2017,12,31"...
all YTD functions default to the last/max year in your calendar
Hi @mpm000
Do you have a separate date table? If not, you can easily create one using the CALENDARAUTO() function.
You can then create a relationship between your new date table and your 'Payment_Detail_Daily' table.
Finally try using the Date column from the new date table in your forumla and see if that helps 🙂
I should have also mentioned that it was a gauge chart visual that I was using. I have the YTD prior set as the goal and the YTD as the value.
Your Measures need to reference the Date column in your Calendar Table
Total EV Sales YTD = CALCULATE ( 'Payment_Detail_Daily'[Total EV Sales], DATESYTD ( 'CalendarTable'[Date] ) ) Total EV Sales YTD Last Year = CALCULATE ( [Total EV Sales YTD], SAMEPERIODLASTYEAR ( 'CalendarTable'[Date] ) )
With this change your Measures should work even after you apply slicers/filters
Good Luck!
BTW Always add a table name before a column name, and never add a table name before a measure name
EDIT: Here's a demo with 2 Gauges and a Slicer.
The first Gauge uses Measures that reference the 'CalendarTable'[Date] and the second does not!
Thanks for your help! I originally had done that, but it didn't work. I was using 'Date'[Date]. Here's what I used to create my date (calendar) table:
Date = ADDCOLUMNS
( CALENDAR ( "2013,01,01", "2025,12,31" ),
"Date Key", FORMAT ( [Date], "YYYYMMDD" ),
"Year", YEAR ( [Date] ),
"Month number", FORMAT ( [Date], "MM" ),
"Year Month number", FORMAT ( [Date], "MM-YYYY" ),
"Month Name Long", FORMAT ( [Date], "mmmm" ),
"Day of Week Number", WEEKDAY ( [Date] ),
"Day of Week", FORMAT ( [Date], "dddd" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"Year Quarter", FORMAT ( [Date], "Q" )
& "-" & FORMAT ( [Date], "YYYY" ) )
Could you let me know if you see something that I've obviously missed? Thanks!
CALENDAR ( "2013,01,01", "2017,12,31"...
all YTD functions default to the last/max year in your calendar
Actually I think I figured it out. I dropped now() in for the end date instead. That fixed it for me. Thank you so much for your help!!!!
I've also setup the relationship between the date field in the date (calendar) table and the date field in the payment_detail_daily table.
Hey Phil,
Thanks for the repsonse. Yes I do have a separate date table. The YTD and the YTD year prior are working perfectly as long as I don't apply a report or page filter. However, I have several offices and want to be able to filter between them easily to see these numbers for each one individually. Only I select 1 of the locations in the filter I get that error.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
84 | |
84 | |
72 | |
49 |
User | Count |
---|---|
143 | |
131 | |
109 | |
64 | |
55 |