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

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
mpm000
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
Sean
Community Champion
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 Smiley Happy

Good Luck! Smiley Happy

 

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!

 

YTD vs YTD SPLY (Not Referencing Calendar Table).gif

View solution in original post

Sean
Community Champion
Community Champion

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

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

View solution in original post

9 REPLIES 9
Phil_Seamark
Microsoft Employee
Microsoft 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 🙂


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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
Community Champion
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 Smiley Happy

Good Luck! Smiley Happy

 

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!

 

YTD vs YTD SPLY (Not Referencing Calendar Table).gif

mpm000
Frequent Visitor

@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
Community Champion
Community Champion

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

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

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

mpm000
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?
mpm000
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.

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.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.