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

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! Appreciate your Kudos!!
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

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! Appreciate your Kudos!!
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


Highlighted
Community Support
Community Support

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

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
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors