Forum Discussion

sloeppky_pdga's avatar
sloeppky_pdga
Regular Visitor
2 years ago
Solved

Calculating records between 2 dates while allowing slicers

I'm working on a membership dashboard where I have a date dimension table, a member dimension table and a membership transaction table. The membership transactions have a start and end date field based on the amount of time the member subscribed for. I've been able to accurately calculate a measure which shows a running total of how many active memberships there are however, in so doing, it seems like my slicers no longer work. I've been able to program in a parameter for one dimension I want to filter on but I don't really want to be building out parameters for each dimension I want to filter on.

 

Is there a better way I can calculate this which will still allow me to use slicers?

This is my current DAX measure, the purple is the component I added to allow for reporting on gender, however, using this method means stacked bar/area charts don't work as expected.

Active Membership = CALCULATE(
    DISTINCTCOUNT('DIM Member'[MemberNum]),
    FILTER(
        ALL('FACT Member Transaction'),
        'FACT Member Transaction'[EffectiveDate] <= MAX('Date'[DATE])
        && 'FACT Member Transaction'[ExpirationDate] > MIN('Date'[DATE])
    )
,'DIM Member'[Gender] = SELECTEDVALUE('_Parameter Gender'[_Parameter Gender Fields])
)
  • For those that may stumble upon this in the future, the answer is quite simple, added all('Date') instead of all('FACT Member Transaction')

1 Reply

  • For those that may stumble upon this in the future, the answer is quite simple, added all('Date') instead of all('FACT Member Transaction')