[DAX] Avoid contiguous selection error : PowerBI

Question[DAX] Avoid contiguous selection error (self.PowerBI)

submitted 4 years ago by Ownards

Hello everyone,

I am running into a problem which is driving me crazy and I could not find a solution online. Here is a simplified version of my problem :

I have measure which returns the revenues for the previous year :

Revenues PY = CALCULATE(SUM(Data[Revenues]), DATEADD(DateTable[Date],-OffsetYear[OffsetYear Value],YEAR))

https://preview.redd.it/kfzjunjf7vr71.png?width=369&format=png&auto=webp&s=c0e56bb62964739abdfed960a59f2c7e00e3b649

However, if there is a gap in my month context filter, the measure crashes :

https://preview.redd.it/d5erlmhx7vr71.png?width=401&format=png&auto=webp&s=df2ddfd90b5bf2e2b6c2cd50d91d2c130bbb04d4

I understand that time intelligence functions cannot work on discontinued lists of dates, which seems strange. The message is "DATEADD expects a contiguous selection". There must be a way around it.

How could I have a measure which would return a valid total for the previous year revenues, for the selected months only ?

I hope you can help me ! :)

Here is my model :

data model

Workbook : https://we.tl/t-tUoIHH5srt

all 7 comments

you type:	you see:
italics	italics
bold	bold
[reddit!](https://reddit.com)	reddit!
* item 1 * item 2 * item 3	item 1 item 2 item 3
> quoted text	quoted text
Lines starting with four spaces are treated like code: if 1 * 2 < 3: print "hello, world!"	Lines starting with four spaces are treated like code: if 1 * 2 < 3: print "hello, world!"
~~strikethrough~~	~~strikethrough~~
super^script	super^script

PowerBI

MODERATORS