cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
oldhasbeen Member
Member

DATEADD "expects a contiguous selection when the date column is not unique, has gaps..."

I'm trying out some DAX functions on the following data:

IDAXF4.PNG

I've set up this measure

Sales YTD = Calculate(TOTALYTD(SUM(Sales[Sale£]),Sales[OrderDate]))

and used it in the following table

DAXF6.PNG

Now I'd like to add in sales for the previous year, so I've created a new measure using DATEADD:

Sales LY = Calculate(SUM(Sales[Sale£]),DATEADD(Sales[OrderDate],-1,YEAR))

But when I try to add this to the above table, I get:

 

DAXF7.PNGDAXF8.PNG

This doentn't make much sense to be.  What does it mean that "the date column is not unique" - does it expect unique date values? In the context of my example, this is nonsense as there are many orders on some days and none on many other days!

 

Please help!! 

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
HotChilli New Contributor
New Contributor

Re: DATEADD "expects a contiguous selection when the date column is not unique, has gaps...&amp

Are you using the Date from the Date table in the visualisation?

oldhasbeen Member
Member

Re: DATEADD "expects a contiguous selection when the date column is not unique, has gaps...&amp

In the measure, yes, but not in the Table, where I use "Order Date", from the Sales table:::

 

Sales SPLY = Calculate([Total Sales],SAMEPERIODLASTYEAR('Date Table'[Date]))

 

DAX SPLY.PNG

 

So I've tried using the Date from "Dates Table" and it seems to work:

 

Last Year - CORRECT.PNG

 

I'm not entirely sure why one way works and another doesn't, I need to mug up on DAX date processing

 

7 REPLIES 7
oldhasbeen Member
Member

Re: DATEADD "expects a contiguous selection when the date column is not unique, has gaps...&quo

HotChilli New Contributor
New Contributor

Re: DATEADD "expects a contiguous selection when the date column is not unique, has gaps...&quo

Time intelligence works best when you have a Date table.

Depending on your data, DATEADD won't play nicely if you don't use a Date table.  That's what the message is all about.

In the visualisation that is complaining about the Sales LY measure, there isn't really a concept of current year and last year because there is no date field  in the table.

You can see in the other visualisation that has 'Sales LY', and doesn't complain, that the results are patchy as there isn't  equivalent data from the day a year previous for many of the rows.

oldhasbeen Member
Member

Re: DATEADD "expects a contiguous selection when the date column is not unique, has gaps...&amp

Thnks for this HotChilli.

 

I'm now trying to set up a Dates table, but am getting weird results at the first step - as you wan see from the amage below, I'm aiming to set up a dates table for all days between 2010 and 2035 but Power BI in starting my table on 23/6/1906(!) and ending  it on 16/016/1937 (!!)

 

DAXCAL1.PNG

 

Any ideas?

 

Thanks!

oldhasbeen Member
Member

Re: DATEADD "expects a contiguous selection when the date column is not unique, has gaps...&amp

Just worked out the answer to this, I've got the parameters in the wrong  order. 

 

Duh!!

oldhasbeen Member
Member

Re: DATEADD "expects a contiguous selection when the date column is not unique, has gaps...&amp

OK, now I've created a Dates Table and related it for the "Order Date" on the "Sales" table. 

DAX date telns.PNG

 

I've also created a new Measure:

 

Sales SPLY = Calculate([Total Sales],SAMEPERIODLASTYEAR('Date Table'[Date]))

 

But when I use this measure in a simple Order date: Total sales table, it seems to ignore the SAMEPERIODLASTYEAR parameter - "Total" Sales always equals "Sales SPLY"

 

DAX SPLY.PNG

 

PBI Desktop Doc is at https://1drv.ms/u/s!AmxJyApgEAcYgtUoRdtcWBWIJcYTrQ

 

Any ideas?

HotChilli New Contributor
New Contributor

Re: DATEADD "expects a contiguous selection when the date column is not unique, has gaps...&amp

Are you using the Date from the Date table in the visualisation?

oldhasbeen Member
Member

Re: DATEADD "expects a contiguous selection when the date column is not unique, has gaps...&amp

In the measure, yes, but not in the Table, where I use "Order Date", from the Sales table:::

 

Sales SPLY = Calculate([Total Sales],SAMEPERIODLASTYEAR('Date Table'[Date]))

 

DAX SPLY.PNG

 

So I've tried using the Date from "Dates Table" and it seems to work:

 

Last Year - CORRECT.PNG

 

I'm not entirely sure why one way works and another doesn't, I need to mug up on DAX date processing

 

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 403 members 3,910 guests
Please welcome our newest community members: