How to overcome error "Function 'DATEADD' expects a contiguous selection..." with bi-dir date table

[Draginko]

I have dashboard with different views on email traffic analytics, where I have one visual displaying evolution of reads for first week, and in order to make it work I have a bi-directional relationship between date table and fact table, with second relationship from date table to dim_reads table, which is active only in one specific view. Here is my data model, most of it comes from ODATA transfer, with one dimensional excel stored at onedrive, and two tables which are generated in PQ (one of it is date table):

All charts, measures and different pages works fine, but now I am working on a trending view, where I will compare different quarters for different metrics, with possibilities to display per different tags (audience, group, communication topic, etc...). Here is the snippet of planned comparision, where user can select the quarter which they want to compare against previous quarter:

And as you can see, all is working well and calculations are correct. The problem starts, when I want to filter it by e.g. audience:

As you can see, all calculations are again correct, with the expection of displayed quarters, here is the full error message:

Here are the measures which are used for quarters:

This one is used in numeric calculations, and it is the quarter which will be selected by user:
_SelectedQrt =

CALCULATE(

    QUARTER(

        MAX('Calendar'[Date])

        )

    )  

And in the card visualisation bellow chart is taking another column from date table, which will display quarter-year in desired format.

Here is the measure for taking previous quarter of selected one, also used in calculations:

_PreviousQrt =

CALCULATE(

    (fct_SendMessages[_SelectedQrt]),

    DATEADD('Calendar'[Date], -1, QUARTER)

    ) 

And then another measure which is used for card vis:

_PreviousQrtKPI =

"Q" & [_PreviousQrt] & " '" & RIGHT(YEAR(MAX('Calendar'[Date])), 2)

Of course, this can be fixed by changing the bi-dir relationship from date table into single one, but then it will break whole logic on other page, which I dont want.

And here are my questions:

1. Why it breaks only that quarter name measure, and not the numeric calculations, which are also using selected/previous quarters in their logic?
2. How to fix this? Ideally, without changing that bi-dir, because then I have to rework quite a chunk of items and currently not sure how to achieve desired output without bi-dir. But given the fact, that all numeric calculations are working well, it shouldnt be necessary to change the relationship, and I hope it will be just some simple fix 😄 

Let me know, if you have questions or need some more information related to this, and I thank you in advance if you give this a thought, I am stucked with this already for two days.

[NikhilChenna]

Hi @Draginko ,

As the error says you can't have periods selected that are not contiguous to one another. So, if you select Jan and Feb in the same year it'll work, but if you select Jan and March, say, in the same year, it'll not work. 

But to solve this i suggest to check the relationships once again the issue is there only.

Regards,

Nikhil Chenna

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!

[PBIDevFW]

You won't get any kudos with responses like this. Why bother?

[Draginko · Accepted solution]

Thank you for your message @NikhilChenna .

However, in my case, due to the narrow timeframe of dataset, currently I have data only for this year, and in every situation I want to compare two following quarters, so they are indeed contiguous. 

And also, based on this logic this should break both quarter names and numeric calculations.

In the meantime I found one, not very elegant tho, solution, which after initial testing seems working fine.

I have replaced the previously used measure for giving previous of the selected quarter:

PreviousQrt =

CALCULATE(

    (fct_SendMessages[SelectedQrt]),

    DATEADD('Calendar'[Date], -1, QUARTER)

    )

And replaced with this measure:

PreviousQrt_viaMax =

CALCULATE(

    MAX('Calendar'[Quarter])

) -1

As I have mentioned, not very elegant, but it seems it is doing the required job, so far all is good. I think we can close this thread.

Thank you

[NikhilChenna]

Hi @Draginko ,

As the you said it seems it is doing the required job, so far all is good. I think we can close this thread.

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!