Counting rows between dates in different columns

[MichaelBauld]

Hi there,

I’m trying to track the number of active members over time by using two different date columns within a single fact table:

1. [Valid From]
2. [Cancelled At]

FYI The membership fact table is related to a calendar table with an active relationship between [Valid From] and [Calendar Date] and inactive relationships between a couple of other dates, including [Cancelled At].

I’m still new to Power BI so my initial thought was I would need to create a calculated column alongside the date range and have each row evaluate the number of rows within the membership fact table which meet the following criteria:

[Calendar Date] >= [Valid From] && <= [Cancelled At]  

OR

[Calendar Date] >= [Valid From} && [Cancelled At] = BLANK.

This would then allow me to select any date from my calendar table and return the active membership count at that time. The only problem is I haven’t been able to figure out how to do a count between dates across two different columns as well as nesting the OR statement to cater for memberships that are still active. 

Any help in how to do this would be extremely useful!

Kind regards

Michael

[amitchandak]

@MichaelBauld , Are looking something similar to active employee in this blog

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[MichaelBauld]

@amitchandak - thank you for the really helpful HR related post. It is exactly what I am trying to accomplish. However, I have input what I beelive is the correct DAX but am getting some strange outputs. The following is what I drew from your blog post:

Member Count = CALCULATE (COUNTX (FILTER(Member_Warehouse,Member_Warehouse[validfrom]<=MAX('Calendar MASTER'[Date]) && Member_Warehouse[End Date]>MAX('Calendar MASTER'[Date])),Member_Warehouse[validfrom]))

I modified the expression in your blog by removing the filter for BLANK end dates. The [End Date] column is now a calculated column which pulls its value from one of two different date fields within the fact table ([cancelled at] and [valid to]).

"End Date", each if [cancelledat] = null then [validto] else [cancelledat]),

To cut a long story short, the calculated column means there are no blank values.

However, the counts I get back are very wrong. Each date should be returning in the region of 170-200k of rows meeting the criteria: 

I'm not sure if I have made an error in modfiying the expression, or whether there is something else I am not picking up, but if you have any thoughts I would be really grateful to hear them!

[MichaelBauld]

I'm still struggling to locate the problem of getting a count of all rows between the two selected dates by date. I have pulled some sample data and created a simple model of just the membership table and a date table.

https://we.tl/t-P5Mo7XYdZa

As ever, really appreciate any help the community can provide. 

Kind regards

Michael

[v-cgao-msft]

Hi @MichaelBauld ,

Please try changing the relationship between the [Date] column and the [validfrom] column to inactive, then check the result.

Attach the PBIX file for reference. Hope it helps.

If this doesn't work for you or I misunderstand your needs, please consider sharing more details about it. And it would be great if there was a sample file without any sensitive information here.

It makes it easier to give you a solution.

1. Sample (dummy dataset) data as text, use the table tool in the editing bar.
2. Expected output from sample data.
3. Explanation in words of how to get from 1. to 2.

Best Regards,
Community Support Team_Gao

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[MichaelBauld]

@v-cgao-msft Thanks for this. Your solution seems to work pretty well!

Given that I have to remove the relationship to make this work I wonder if there is a way to 'turn off' the relationship for only this measure? I have a number of measures which use the relationship between the date table and the membership fact table so would prefer to keep the relationship in place for these as opposed to using USERELATIONSHIP in ever measure.

Cheers

Michael

[v-cgao-msft · Accepted solution]

Hi @MichaelBauld ,

Please add a [index] column for 'Member_Warehouse' table and change the relationship status to Active.

Then create a measure.

Current Employees = 
CALCULATE (
    COUNTX (
        FILTER (
            'Member_Warehouse',
            'Member_Warehouse'[validfrom] <= MAX ( 'Date Table'[Date] )
                && (
                    ISBLANK ( 'Member_Warehouse'[End Date] )
                        || 'Member_Warehouse'[End Date] > MAX ( 'Date Table'[Date] )
                )
        ),
        ( 'Member_Warehouse'[Index] )
    ),
    CROSSFILTER ('Member_Warehouse'[validfrom],'Date Table'[Date], NONE)
)

Drag the measure and [day] columns together into the visual and check the results.

Best Regards,
Community Support Team_Gao

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data