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.

ahoggatt42's avatar
ahoggatt42
Regular Visitor
2 years ago

I also created a calendar table (Bookings Goal)

 

I joined all three tables based on their dates

 

And using the same measure I used before:

Intl Cumulative UO Bookings (Month) =

var AR =
CALCULATE(
    SUM('AR Data'[Sum of True Price]),
    FILTER(
        ALL('AR Data'),
        'AR Data'[Order Date]<= MAX( 'AR Data'[Order Date]) &&
        'AR Data'[Order Month] = MAX('AR Data'[Order Month])&&
        'AR Data'[Order Year] = MAX('AR Data'[Order Year])&&
        'AR Data'[Order Type] = "UO"
        ))
var SO =
CALCULATE(
    SUM('SO Data'[Sum of True Price]),
    FILTER(
        ALL('SO Data'),
        'SO Data'[Trans Date] <= MAX( 'SO Data'[Trans Date]) &&
        'SO Data'[Trans Month] = MAX('SO Data'[Trans Month])&&
        'SO Data'[Trans Year] = MAX('SO Data'[Trans Year])&&
        'SO Data'[Order Type] = "UO"
        ))


Return
SO+AR

I get this result:

 


I am expecting that each column is a running sum by date.

I have this working for just one location now without any issues using the same code just without adding another site to it:

 

Intl Cumulative UO Revenue (Month) =
CALCULATE(
    SUM(HistDetailAll[True Price]),
    FILTER(
        ALL(HistDetailAll),
        HistDetailAll[actshipdate]<= MAX( HistDetailAll[actshipdate]) &&
        HistDetailAll[Actual Month] = MAX(HistDetailAll[Actual Month])&&
        HistDetailAll[Actual Year] = MAX(HistDetailAll[Actual Year]) &&
        HistDetailAll[Order Type] = "UO"
        ))