cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
FrankMcQ Frequent Visitor
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
Community Support Team

Re: Creating relationships based on a sum calculation

Hi @FrankMcQ 

Create a calendar table,

Capture5.JPG

calendar =
ADDCOLUMNS (
    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,

Capture4.JPG

create a measure

Measure = SUM(invoices[Amount])

Capture3.JPG

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.

View solution in original post

5 REPLIES 5
Super User IV
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]))
FrankMcQ Frequent Visitor
Frequent Visitor

Re: Creating relationships based on a sum calculation

Yes, the formula works with no problems.

Super User IV
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.

Community Support Team
Community Support Team

Re: Creating relationships based on a sum calculation

Hi @FrankMcQ 

Create a calendar table,

Capture5.JPG

calendar =
ADDCOLUMNS (
    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,

Capture4.JPG

create a measure

Measure = SUM(invoices[Amount])

Capture3.JPG

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.

View solution in original post

FrankMcQ Frequent Visitor
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!

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.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors