cancel
Showing results for
Did you mean:

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

Frequent Visitor

## YTD and YTD prior with filter

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!

2 ACCEPTED SOLUTIONS
Community Champion

@mpm000

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

• Column Reference:  TableName[Column Name]
• Measure Reference:  [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!

Community Champion

CALENDAR ( "2013,01,01", "2017,12,31"...

all YTD functions default to the last/max year in your calendar

9 REPLIES 9
Employee

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 🙂

Proud to be a Datanaut!

Frequent Visitor

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.

Community Champion

@mpm000

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

• Column Reference:  TableName[Column Name]
• Measure Reference:  [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!

Frequent Visitor

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:

( 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!

Community Champion

CALENDAR ( "2013,01,01", "2017,12,31"...

all YTD functions default to the last/max year in your calendar

Frequent Visitor

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!!!!

Frequent Visitor
Ahhh that makes a lot of sense. Is there a way for me to write this so that it continually populates dates or will I just have to manually update the calendar each year?
Frequent Visitor

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.

Frequent Visitor

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.