Forum Discussion

ahoggatt42's avatar
ahoggatt42
Regular Visitor
2 years ago
Solved

Cumulative Sum from Two Different Tables

My company is currently using two completely seperate systmes to track sales in US vs sales for the rest of the world. Additionally we have two different types of sales, OP and UO orders. I am trying...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi ahoggatt42 ,

    1. Create a calculation table and obtain data that meets the conditions.

    Table = 
UNION(SELECTCOLUMNS(
    FILTER('AR Data','AR Data'[Order Type] = "UO"),
    "MyDate",'AR Data'[Order Date],
    "MyYear",'AR Data'[Order Year],
    "MyMonth",'AR Data'[Order Month],
    "MyPrice",'AR Data'[Sum of True Price]),
    SELECTCOLUMNS(
        FILTER('SO Data','SO Data'[Order Type] = "UO"),
        "MyDate",'SO Data'[Trans Date],
        "MyYear",'SO Data'[Trans Year],
        "MyMonth",'SO Data'[Trans Month],
        "MyPrice",'SO Data'[Sum of True Price]))

     

    2. Create measure.

    Measure =
CALCULATE(SUM('Table'[MyPrice]),FILTER(ALL('Table'), 'Table'[MyYear] = MAX('Table'[MyYear]) && 'Table'[MyMonth] = MAX('Table' [MyMonth]) &&'Table'[MyDate] <= MAX('Table'[MyDate])))

    If your Current Period does not refer to this, please clarify in a follow-up reply.

     

    Best Regards,

    Clara Gong

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi ahoggatt42 ,

Based on your description, I created three tables and created relationships for them.

 

Subsequently a measure similar to the one in your description was written and it gave the correct results.

Measure =
VAR test1 =
CALCULATE(SUM('Table A'[Price]),FILTER(ALL('Table A'),'Table A'[date] = MAX('Table A'[date]) && 'Table A'[Type] = "A"))
VAR test2 =
CALCULATE(SUM('Table B'[Price]),FILTER(ALL('Table B'),'Table B'[date] = MAX('Table B'[date]) && 'Table B'[Type] = "A"))
RETURN
test1 + test2

 

Therefore, please confirm whether the direct relationship between your tables is correct. Another thing to note is that the date of the intermediate table should be placed in the visual object, which is the Bookings Goal table in your description.

 

Alternatively, there is a way to get the results you want, which requires building a calculation sheet.

Table =
UNION(SELECTCOLUMNS(
FILTER('Table A','Table A'[Type] = "A"),
"MyDate",'Table A'[date],
"MyPrice",'Table A'[Price]),
SELECTCOLUMNS(
FILTER('Table B','Table B'[Type] = "A"),
"MyDate",'Table B'[date],
"MyPrice",'Table B'[Price]))

 

Then create measure and perform summation.

Measure 2 =
CALCULATE(SUM('Table'[MyPrice]),FILTER('Table','Table'[MyDate] = MAX('Table'[MyDate])))

If your Current Period does not refer to this, please clarify in a follow-up reply.

 

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

ahoggatt42's avatar
ahoggatt42
Regular Visitor
2 years ago

Thanks for your reply. I think I need to clarify what my expected outcome is. If I use your sample (For simplicity) I would expect something like this:

1/1/2024  20
1/2/2024  31
1/3/2024  64
1/5/2024 125
1/6/2024 140

 

Based on what you have on your relationships that is how I have mine set up as well. I can put together some samples of my data if that would help.