Counting "Active" Rows in a Time Period
Recently, a customer sent me a question regarding a DAX problem they were working on. They have a Members table in their model that includes attributes of "Start Date" and "End Date" for each member. The question they needed to answer was "How many active members did we have in [fill in the blank]?" I thought this was a pretty interesting question and it seemed like it ought to be pretty easy to do in DAX.
They were trying to get it to work using relationships between the start and end dates columns on the Members table and their Date table. Their date table also has "Month Start" and "Month End" columns. They were trying to use these columns in conjunction with the RELATED function to determine if the member was active by comparing the member start and end dates to the month start and end dates.
Read more...