Forum Discussion

PowerPaddy's avatar
PowerPaddy
Frequent Visitor
8 years ago

Count of Rows that fall within a date range

Hello, I'm really strugging with a dax expression that seems like it should be fairly simple.

I have a table of Insurance Policies, 1 row for each policy. Each row has a POLICYSTARTDATE and POLICYENDDATE.
I want to show a daily/monthly count of Live policies, ie choose a date and display the number of rows for which that date falls within POLICYSTARTDATE and POLICYENDDATE.

Any help greatly appreciated.

Need Help

2 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    PowerPaddy

     

    Try this measure

     

    Adjust the mydate according to your needs. You can link it to a slicer as well

     

    Measure =
VAR mydate =
    DATE ( 2017, 1, 22 )
RETURN
    COUNTROWS (
        FILTER (
            ADDCOLUMNS (
                TableName,
                "Live/Dead", IF (
                    mydate >= TableName[Policy Start Date]
                        && mydate <= TableName[Policy End Date],
                    "Live",
                    "Dead"
                )
            ),
            [Live/Dead] = "Live"
        )
    )