Complex table filter within a measure

[robbielambert]

Hi,

I have two tables identical in design to the examples in the image the below. Table 1 contains ~100K rows, table 2 contains ~500K rows. 

I am looking to build measures which would allow me to report on Transaction Amount by Transaction Date (from table 2) for a given Category from table 1. I need 1 measure which reports the amount on the Transaction Date, and another which reports the amount cumulatively up to that Transaction Date.

There are only 3 categories, so I am happy to have a measure for each category (i.e.  Category A Transaction Amount andCategory A Transaction Amount Cumulative), rather than have the category as a dimension.

I think the logic to build the measures for Category A should be:

1. Chose a date from the transactions table

2. Filter the categories table for rows where the from date is less or equal to the transaction date and the To Date is greater than the transaction date and Category = A

3. Sum all values in the transactions table for the sub set of customers generated by part 2 (either cumulatively to that date, or specifically for that date).

Unfortunately, I am failing miserably at step 2. ANy help would be incredibly gratefully recieved.

Thanks 

[Pragati11]

Hi @robbielambert ,

Have you tried creating relationship between these 2 tables on "Customer" and then try performing the 3 steps?

Also, can you share the DAX or calculation you are using for the 3 steps you mentioned?

If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂

Thanks,

Pragati

[robbielambert]

Hi @Pragati11,

Thanks for getting in touch. 

I have a join on customer between the two tables as you describe. It is then the step of defining which customers are in a specific category on a given date that is where i am failing.

The approach I have taken is to create a table variable which filters for the accounts in a specifc category on a specific date, and then try to filter the transactions table using the list I have created. However, I cannot seem to restrict the transactions table by the table variable I have created. I'm sure there must be an easier way!!

Here is my DAX:

Transactions Amount =

VAR VarTable = CALCULATETABLE('Categories','Categories'[From Date] <= EOMONTH(today(),-2), 'Categories'[To Date] > EOMONTH(today(),-2),'Categories'[category] = "A")

RETURN

CALCULATE(SUMX(CALCULATETABLE('Transactions',EXCEPT(VALUES('Transactions'[Customer]), VALUES(I want to reference VarTable here))),'Transactions'[Amount]))

Thanks