CODEWITHSUNDEEP
powerbidax
Daniélle Truter
Daniélle Truter

Reputation: 1

DAX Count Rows if Start Date is before selected Date in slicer

I have a sample set of HR data which I am using to practice in Power BI. One of the requirements are to indicate the number of vacancies filled for a selected period. There is no indicator to use or filter on for filled vacancies, so I want to build a measure that calculates the vacancies filled by counting the row if the start date associated with the vacancy is smaller/before the date that the user is filtered on in the slicer - basically assuming that the position has been filled on the start date.

Here is the formula I was trying to use. It only returns the number of vacancies for the day that is selected in the slicer, nothing prior. I noticed that the formula doesn't work if I remove the '=' operator, so basically it is only showing dates that are equal to one another, not smaller. If I remove the '=' then I get a blank result.

Then, how do I get this to work when filtered on a month, not a date? If I don't filter on a date, then I also get a blank result.

Vacancies Filled = 
CALCULATE(
    COUNT(EmployeeVacancies[ObjectID]),
    FILTER(
        EmployeeVacancies, 
        EmployeeVacancies[Start date] <= SELECTEDVALUE('Date'[Date])
    )
)

My Date table is marked as a Date table, and it has an active one to many relationship with the EmployeeVacancies table through the Date field.

I would really appreciate any help!

Upvotes: 0

Views: 1416

Answers (1)

Amira Bedhiafi
Amira Bedhiafi

Reputation: 1

When you chose to use SELECTEDVVALUE, you forgot that you only compare the start date of vacancies with a single selected date from your slicer, rather than a range of dates. So your measure is only counting vacancies for the specific date selected in the slicer, not for all dates up to and including that date. Try the following :

 Vacancies Filled = 
    CALCULATE(
        COUNT(EmployeeVacancies[ObjectID]),
        FILTER(
            EmployeeVacancies, 
            EmployeeVacancies[Start date] <= MAX('Date'[Date])
        ),
        ALLSELECTED('Date'[Date])
    )

Upvotes: 0

Related Questions