cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
xmark Regular Visitor
Regular Visitor

Reporting Periods and Circular Dependencies

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:

power-bi-error.PNG

 

 

 

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.

7 REPLIES 7
ManNVSM Member
Member

Re: Reporting Periods and Circular Dependencies

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 Regular Visitor
Regular Visitor

Re: Reporting Periods and Circular Dependencies

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. 

 

 

ManNVSM Member
Member

Re: Reporting Periods and Circular Dependencies

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 Regular Visitor
Regular Visitor

Re: Reporting Periods and Circular Dependencies

Hi @ManNVSM 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 Smiley Happy

 

ManNVSM Member
Member

Re: Reporting Periods and Circular Dependencies

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 Regular Visitor
Regular Visitor

Re: Reporting Periods and Circular Dependencies

Hi @ManNVSM 

 

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.

ManNVSM Member
Member

Re: Reporting Periods and Circular Dependencies

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

 

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors