cancel
Showing results for
Did you mean:
Frequent Visitor

## Creating relationships based on a sum calculation

Hi

I have a table of milestones (because our financial calendar doesn’t match the regular calendar):

Financial Year          Name              Period             Start Date      End Date

2018                        2018-2019       Q1                   20181001        20181231

2018                        2018-2019       Q2                   20190101        20190331

2018                        2018-2019       Q3                   20190401        20190630

2018                        2018-2019       Q4                   20190701        20190930

2018                        2018-2019       Year                 20181001        20190930

2019                        2019-2020       Q1                   20191001        20191231

…

I also have a table of invoices:

Date                  Amount                   Job ID

01/11/2017       20.87                        0001

01/12/2017       32.31                        0002

...

Finally, I have a table of jobs. The table has a relationship to the invoices table via Job ID:

Job ID            Job Team

0001              Team #A

0002              Team #B

0003              Team #A

I’ve created columns in the financial milestones table to sum the invoices in that period... I'm using the following instruction:

Invoices = CALCULATE(SUMX(FILTER('Invoices',
AND('Invoices'[Invoice Date] >= SELECTEDVALUE('[Start Date]), 'Invoices'[Invoice Date] <  SELECTEDVALUE(‘End Date’))),
'Invoices'[Amount]))

I now want to slice the calculated result by Job Team, but I keep getting the error:

‘Can’t determine the relationship between the fields’

I’m think this is because the Invoices calculation doesn’t create/have a relationship to the invoices table.

Is there a way to create this relationship?

Any suggestions would be gratefully received.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: Creating relationships based on a sum calculation

Create a calendar table,

``````calendar =
CALENDARAUTO (),
"fiscal year", IF ( MONTH ( [Date] ) >= 10, YEAR ( [Date] ), YEAR ( [Date] ) - 1 ),
"fiscal quarter", SWITCH (
TRUE (),
MONTH ( [Date] ) >= 10
&& MONTH ( [Date] ) <= 12, "Q1",
MONTH ( [Date] ) >= 1
&& MONTH ( [Date] ) <= 3, "Q2",
MONTH ( [Date] ) >= 4
&& MONTH ( [Date] ) <= 6, "Q3",
MONTH ( [Date] ) >= 7
&& MONTH ( [Date] ) <= 9, "Q4"
)
)
``````

Create columns in this table

``````start date = CALCULATE(MIN('calendar'[Date]),ALLEXCEPT('calendar','calendar'[fiscal year],'calendar'[fiscal quarter]))

end date = CALCULATE(MAX('calendar'[Date]),ALLEXCEPT('calendar','calendar'[fiscal year],'calendar'[fiscal quarter]))``````

create relationship as below,

create a measure

``Measure = SUM(invoices[Amount])``

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 REPLIES 5
Super User IV

## Re: Creating relationships based on a sum calculation

Is this formula saved without error

``````Invoices = CALCULATE(SUMX(FILTER('Invoices',
AND('Invoices'[Invoice Date] >= SELECTEDVALUE('[Start Date]), 'Invoices'[Invoice Date] <  SELECTEDVALUE(‘End Date’))),
'Invoices'[Amount]))``````

I doubt powerbi support AND as function

``````Invoices = CALCULATE(SUMX(FILTER('Invoices',
('Invoices'[Invoice Date] >= SELECTEDVALUE('[Start Date]) && 'Invoices'[Invoice Date] <  SELECTEDVALUE(‘End Date’))),
'Invoices'[Amount]))``````

Did I answer your question? Mark my post as a solution!
In case it does not help, please provide additional information and mark me with @
My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP,
HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Super User! Linkedin

Frequent Visitor

## Re: Creating relationships based on a sum calculation

Yes, the formula works with no problems.

Super User IV

## Re: Creating relationships based on a sum calculation

To help you further I need pbix file. If possible please share a sample pbix file after removing sensitive information.

Did I answer your question? Mark my post as a solution!
In case it does not help, please provide additional information and mark me with @
My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP,
HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Super User! Linkedin

Community Support Team

## Re: Creating relationships based on a sum calculation

Create a calendar table,

``````calendar =
CALENDARAUTO (),
"fiscal year", IF ( MONTH ( [Date] ) >= 10, YEAR ( [Date] ), YEAR ( [Date] ) - 1 ),
"fiscal quarter", SWITCH (
TRUE (),
MONTH ( [Date] ) >= 10
&& MONTH ( [Date] ) <= 12, "Q1",
MONTH ( [Date] ) >= 1
&& MONTH ( [Date] ) <= 3, "Q2",
MONTH ( [Date] ) >= 4
&& MONTH ( [Date] ) <= 6, "Q3",
MONTH ( [Date] ) >= 7
&& MONTH ( [Date] ) <= 9, "Q4"
)
)
``````

Create columns in this table

``````start date = CALCULATE(MIN('calendar'[Date]),ALLEXCEPT('calendar','calendar'[fiscal year],'calendar'[fiscal quarter]))

end date = CALCULATE(MAX('calendar'[Date]),ALLEXCEPT('calendar','calendar'[fiscal year],'calendar'[fiscal quarter]))``````

create relationship as below,

create a measure

``Measure = SUM(invoices[Amount])``

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Highlighted
Frequent Visitor

## Re: Creating relationships based on a sum calculation

This is a great, many thanks. As I suspected, I just had a bad design - I think this is a much more elegant solution!

Announcements

#### 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!

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?

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

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

#### Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors