cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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]))




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


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.





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

Highlighted
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.

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