How to Sum Values based on (Value -1) from other column

Question

Hello Community Members,&nbsp;I am struggeling with the following. I have a fairly simple table. No date dimensions unfortunately.&nbsp;The rows contain a period number. Which can be from 1 - 12. I need a measure where the amount is summed.&nbsp;It's conditional. Where period is 1 it should sum the amount where period is 1. If period is 2 it should Sum the amount of Period 2 - Period 1. If period is 3 it should Sum the amount of Period 3 - Sum of Period 2 etc.&nbsp;I tried a lot with selected value and MAX, but it gave me the true / false error, so obviously i am doing something wrong.Can anybody help me out?&nbsp;See table below for reference.&nbsp;Kind regards,&nbsp;Jeroen

anonymous · Accepted Answer

Hi&nbsp;JeroenHD,
Can you please share some more detail about these table structures and relationship mapping?
How to Get Your Question Answered Quickly&nbsp;&nbsp;
You can extract the second table relationship key values based on the current selection, and use these extract values as conditions to filter on the first table.
formula =
VAR selected =
    CALCULATETABLE ( VALUES ( Table2[key] ), ALLSELECTED ( Table2 ) )
RETURN
    CALCULATE (
        SUM ( Table1[Amount] ),
        FILTER ( ALLSELECTED ( Table1 ), [key] IN selected )
    )
Regareds,
Xiaoxin Sheng

Forum Discussion

How to Sum Values based on (Value -1) from other column

1 Reply

Related content

Splitting Column values

Matching Column Values

Check column for similar values

Closing value

Filter a column with duplicate values based on the highest Index value