Reporting Periods and Circular Dependencies

[xmark]

Hi all.

My business has reporting periods that are mostly consistent, but not always and as such as defined within a table called "Periods"

Period Columns:

Start, End, Period #, PeriodKey

For example:

July 1 2017, July 30 2017, 1, 2017-1

August 1 2017, August 28 2017, 2, 2017-2

...

I also have a standard date table called Date that links to my actual reporting data (sales, etc.)

My Problem:

I want to create a relationship from the Date table to the Period table, so I want to add a column to the Date table called PeriodKey so that I can create the relationship, which ultimately means I can select a Period for reporting, rather than a date range.

I used this DAX formula to create the new column in the Date table:

PeriodKey = CALCULATE(DISTINCT(Periods[PeriodKey]), FILTER('Periods', DateTable[Date] >= Periods[start] && DateTable[Date] <= Periods[end])) 

Which populates the date table correctly.

But then when I try to create the relationship between the two tables, I get this error:

I have done some reading on this error, and I know that its because of my DAX Query in the Date table, but I dont know how to fix it...

Thanks.

[Anonymous]

Dear @xmark,

That error is correct. Because you use a column in a table Date (with Value Period-Key of Table Period) join with Period-Key, It's still the same column Period-key.

• You should create a formular in Date table, and using on Date table only. Ex: PeriodKey = YEAR('Date'[Date]) & "-" & MONTH('Date'[Date])
• Using new column to make relationship.

Note: make sure your period table have unique PeriodKey.

Regards,

ManNVSM.

[xmark]

Hi ManNVSM,

Thanks for the reply, I can't do that because there is no formula that would work for each date, since the periods are not consistent. 

[Anonymous]

Dear @xmark,

In this case, you should make a new Table base on Date table, summarize with period key (It'll unique), then make relationship between new and Period old.

If you need more help, send me your pbix file, I'll try 🙂

Regards,

ManNVSM

[xmark]

Hi @Anonymous thanks, but I am really not sure how to do that since the summarization rules would not be easy to define. I think I will just manually attribute dates to periods for now, I have spent longer trying to figure this out than just keying data manually would have taken me 

[Anonymous]

Dear @xmark,

You can try this formular

1. Create new column: PeriodKey = YEAR('Date'[Date]) & "-" & MONTH('Date'[Date])
2. Create new Table = SUMMARIZECOLUMNS('Date'[PeriodKey])

Then using this new Table make relationship 🙂

Regards,

ManNVSM.

[xmark]

Hi @Anonymous 

I appreciate your help on this, but like I said, the formula for the period key is NOT that simple, it is not just the month and year together, the periods are formed over 4 week periods, but lock down to each 4th sunday, except and the end and start of the financial year (among other rules). So each year, the forumla for the period calculations are different. Hence why I cant do a simple formula as you suggest.

[Anonymous]

Dear @xmark,

It's seem I can't make sense your issue. You should try to going back the resource data, I think there should be a key between your 2 table, reload them.

If you share the .pbix(delete the sensitive data or create dummy data), I'll try on that file.

Regards,

ManNVSM