Multi aggregation for last date that has data

[Omega]

Hi, 

I am connected to SSAS (live connection) and can't do changes to the model by creating columns. I have the below tables with follwing connection: 

Factor Master [Customer Code] -> Customer Master [Customer Code]

Factor Master [Item Number] -> Item Master [Item Number]

Factor Master [Date] -> Date [Date]

Fact Table [Customer Code] -> Customer Master [Customer Code]

Fact Table [Item Number] -> Item Master [Item Number]

Fact Table [Date] -> Date [Date]

Goal: Acheive city value table

How? 

1. Multiply each value in the fact table with it's corresponding factor table based on customer and item while ignoring the date in the factor master
2. Calculate total sum value for city by adding the (value * factor) of last date for each customer

For example: 

1. New York has two customers, one has last data on 10th and other on 9th
2. By multiplying each item by corresponding factor, 358*9 and 907*5, total value for New York should be 7757

I created the following measures: 

• Measure 1 = Calculating the factor by ignoring the date. Calculate (sum(factor), all (date[date), allexept (Customer master, customer code), allexcept (item master, item code)))
• Measure 2 = calculate the factor by value. Sumx (values (customer code [customer code],sumx (values (item master [item number], Measure 1*sum (Fact Table [Value]))
• Measure 3 = Calculate the max date for each customer by creating the below variables
  • Variable 1 = Calculate (Max (Date[date), filter (Date, Measure 2 <> 0))
  • Variable 2 = Calculate (measure 2, Date[date] = Variable 1) return Variable 2

When I pull measure 3 to city, measure 3 picks the data for the customer that has last date and put it for the city. In the example of New York, I can see the data for last date in Customer A instead of data for last date in customer A + data of last date for customer C

Please help. 

[v-eachen-msft · Accepted solution]

Hi @Omega ,

You could create a new measure to sum your Measure 3.

Measure 4 =
VAR _table =
    ADDCOLUMNS (
        CROSSJOIN (
            VALUES ( 'Customer Master'[Customer] ),
            VALUES ( 'Customer Master'[City] )
        ),
        "v", [Measure 3]
    )
RETURN
    SUMX ( _table, [v] )

I cannot follow your measures to get the result, so I made some changes in these measures.

Here is the result.

Here is the test file for your reference.

[Omega]

Thanks a lot! It's been cracking my head for a while 🙂