Need Modeling and Dax Help for simple DAX IF

[powerbignc]

**I am not able to modify these table, so no calc columns or Power Query***

**Transaction Fact table is over 300 Million Rows and lives in SQL**

**Screenshots of tables are below text**

I have 4 tables

• Dates Table
• Transaction Table
• Store Status Table
• Store Metadata Table.

Dates Table = Simple dates table, Date, month, year etc...

Transaction table (300+ Million Rows) = Shows by date by store, the different transaction amounts

Store Status Table = Shows by data over time what was the store status on that day

Store table = List of stores, location of store etc...

I am trying to model these tables together so that I can write a formula that shows the transaction amount by day for a store ONLY when that store status on that day was "Open". 

So if there was a transaction on 2/21/22 for store A, but the status was "Closed" that day... don't count it.

Dates Table	Transaction Table	Store Status	Store

Question 1: How does this need to be modeled to give me the answer I need?

Question 2: What is the formula I can write to do this?

Here is what I have, but I know this isn't right....

I have two formulas:

Amount = SUM(Transactions[Amt])

Sum Open = IF(MAX('Status'[Status])="Open",[Amount],BLANK())

Unfortunitely I have something wrong, because my "Amount" measure is doubling up and not working and also my totals are the same value which is not right because this store closed on 2/20/22... So I would expect to see "amount" listed ONCE down the rows, and Sum Open list next but the sums need to be summing properly. Sum of Open should be less than "Amount" 

Here is a view in Power BI... and it's not working...

 

 What am I doing wrong, is this modeled properly, how do I write this with DAX?

[amitchandak]

@powerbignc , create a combined column in both transaction table and store table

Date Stote = format([Date], "YYYY-MM-DD") & "-" & [Store]

Use the correct column from both tables and join them

I do not this you will need a store table in that case