Adding a second dimension to an active linked table with a count and date range

Hello all,

My first post on your eminently useful forum. I’ve been using PowerBI Desktop for a few weeks now, so I’m a newbie but have been reading a lot on here!

Background

I’ve a transaction table with customers (AccountID) who purchase something at a site (entry in tblTransaction).

I’ve a complicated overall table structure, but this issue centres around this main Transaction table.

tblTransaction has TimeStamp (withtime), so I’ve created TimestampDate (no time) to enable a relationship to a date table (customtblActiveMembers) where I’ve successfully added a calculated column against each day. It creates an overall distinct count of “active customers” (tblTransation.AccountID) by day (the definition of an “active” customer is one who has bought something in the last 3 months at a site) using this :

Active Members In Last 3 Months = 
/* calculated column counting the unique people with transactions in the last 3 months
    based on formula from: https://sqldusty.com/2015/10/21/10-dax-calculations-for-your-tabular-or-power-pivot-model-part-2/ */
CALCULATE (
    DISTINCTCOUNT ( tblTransaction[AccountID] ),
    DATESBETWEEN (
        customtblActiveMembers[Date],
        DATEADD ( customtblActiveMembers[Date], -3, MONTH ),
        customtblActiveMembers[Date]
    ),
    tblTransaction[AccountID] <> BLANK ()
)

However, my issue is that I can’t work out the right approach to add tblTransaction.SiteID into the data - so that I have “active” customers by site by day. What is the best approach?

So far I’ve tried:

• (probably the best approach) Creating another table with Day by Site (e.g. "1/1/17" "Site 1", "1/1/17" "Site 2" etc.) and add a adding a calculated column against each row. However we all know, powerbi won’t link on 2 active connections to the same table (i.e. Date & Site), so how do I link it? Maybe with 2 tables:
• Tried linking the above mentioned table via 2 separate, unique valued tables – one for date, one for site. For this to work I would need bi directional filtering turned on – but powerbi takes so long to do this, I have to end-task and restart it
• I’m not fluent in DAX enough to create a measure for this … but then wouldn’t the first approach I mentioned with another calculated column be better, else how can site be filtered on (eventually) in a chart?
• Tried creating a combined site+date column in both above tables, but this will only link directly on each row appropriately rather than look back 3 months...

Thanks,

Simon