Forum Discussion

FrankMcQ's avatar
FrankMcQ
Frequent Visitor
6 years ago
Solved

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     ...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi FrankMcQ 

    Create a calendar table,

    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,

    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.

amitchandak's avatar
amitchandak
Super User
6 years ago

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's avatar
FrankMcQ
Frequent Visitor
6 years ago

Yes, the formula works with no problems.

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    Hi FrankMcQ 

    Create a calendar table,

    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,

    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.

    • FrankMcQ's avatar
      FrankMcQ
      Frequent Visitor
      6 years ago

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

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

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