Forum Discussion

MichaelBauld's avatar
MichaelBauld
Frequent Visitor
4 years ago
Solved

Counting rows between dates in different columns

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: [Valid From] [Cancelled At] FYI The membership fact table is...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

MichaelBauld's avatar
MichaelBauld
Frequent Visitor
4 years ago

Anonymous 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

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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