Snapshot fact table, Count Active measures

So the only change I would suggest to your fact table is to insert a high end date like "31-Dec-9999" instead of null when there is no Unsubscribed Date (it just makes the logic simpler.

Then if you create a date table (which is unreated to your fact) you can build measures with the following pattern

Topic Cnt = 
    CALCULATE( 
        COUNTROWS('Fact Topic subscription')
        , filter('Fact Topic subscription', MAX('Date'[Date]) >= 'Fact Topic subscription'[Subscribed date] 
                                         && MIN('Date'[Date]) < 'Fact Topic subscription'[Unsubscribed date] )
    )

You can see in the screenshot below how this picks up the end subscription of the first row and the start of the second row. (you can choose to include the Unsubscribed date in the range by changing the filter statement). You can then add addition Year, Month, Quarter, etc attributes in your Date table and the same measures should continue to work.

---

SuraMan wrote:

Hi d_gosbell ,

I can understand that what needs to happen is to count Fact table rows where Date[date] falls between "Subscribed date" and "Unsubscribed date", but cannot figure out how that translates to the dax expression.

Because the 2 tables do not have a relationship between them if we just did a measure with COUNTROWS('Fact topic subscription') it would return a value of 3 for every date (as there are 3 rows in the example fact table). This is because without a relationship the 'Date' table will not filter the fact table at all.

So the expression pretty much works exactly how you've described it above to filter the fact table.

Topic Cnt = 
   CALCULATE( 
     COUNTROWS('Fact Topic subscription')                                    // Count the rows in the fact table
      , filter('Fact Topic subscription',                                    // filtering the fact table
          MAX('Date'[Date]) >= 'Fact Topic subscription'[Subscribed date]    // where the date is after Subscribed Date
       && MIN('Date'[Date]) < 'Fact Topic subscription'[Unsubscribed date] ) // and before the Unsubscribed Date
    )

The only "trick" is the MIN('Date'[Date])  and MAX('Date'[Date]) references. When you are at the grain of a single day these both return the same value. But if you had months on the rows the MIN would return the first day of the month and the MAX would return the last day of the month.

Is the MIN/MAX the bit that made it hard to understand?