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

    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's avatar
      MichaelBauld
      Frequent Visitor
      4 years ago

      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

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        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