YTD and YTD prior with filter

[mpm000]

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!

[Sean · Accepted solution]

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

[Phil_Seamark]

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 🙂

[mpm000]

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.

[Sean · Accepted solution]

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

[mpm000]

@Sean 

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!

[Sean · Accepted solution]

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

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

[mpm000]

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

[mpm000]

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?

[mpm000]

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.

[mpm000]

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.