Dynamic count based on open and start date with date filter slicer

If anyone can tell me how to upload the pbix, I can send the file, but it tells me it is not supported...

Hi Anonymous 

Things will be different when you filter on a specific Date or a date range. When filtering only a specific date, the current solution 1 and solution 2 should work well as one date could only be a created date, or a closed date, or a date between those two dates. The status is easy to judge. 

However, when you filter to a date range, let's take a month as an example, there are four situations for the status of a job Req. I draw a picture as below. If we select Jan 2020, the status of situation 4 is clear to be "Open" based on your description. 

For situation 1, it was created and closed in the same month and the range between two dates are less than the month range, so do you want to count it as "Open" or "Filled" in Jan 2020?

For situation 2 and 3, their Req open ranges cross two months and neither calendar month is fully covered. So how do you want to count their status in two months? 

These situations also happen when you expand the filtered range to a quarter or a year. Apparently the situation 2 in my example cross two different quarters and different years. You need to determine how you want to count the status for job Reqs that have the situation 1, 2, 3. Give us a specific result "Open" or "Filled" for each month. Then we can help provide a solution that can meet your need. We cannot make the decisions for you on whether it should be "Open" or "Filled". 

Best Regards,
Community Support Team _ Jing

This is a little old, but I have a possible solution. I was struggling to build a rolling count of expiring points (HR management call-out points). The points would expire after a set time window.

I had a date. and a point value. I duplicated the date into "expire date" and added my expiration value to it (i.e. expire date = date + N_days). I had a point value so I duplicated that entire column and multiplied iy by -1 (this makes it a negative counterpart to whatever is in "points"). I did this in excel before importing.

So it would look like this:

Name | Points | NegPoints | Date | ExpireDate

John Doe | 1 | -1 | 5/2/2024 | 7/31/2024

Jane Doe | 1 | -1| 5/11/2024 | 8/9/2024

After importing I unpivoted the two date fields. It lumped them under "Attribute" which I renamed to "DateType"

Name | Points | NegPoints | DateType | PtsDate

John Doe | 1 | -1 | Date | 5/2/2024

John Doe | 1 | -1 | ExpireDate | 7/31/2024

Jane Doe | 1 | -1 | Date | 5/11/2024

Jane Doe | 1 | -1 | ExpireDate | 8/9/2024

How to make the rolling window? I ran into many problems and found precious little help online for it. I created a new column as a measure called PointsCheck. Here's what I did:

CALCULATE(

    SUM(PointsReported[PointsCheck]),  //  PointsCheck is noted above, the -/+ chooser

        FILTER(ALLSELECTED(PointsReported), // This is part of a "choose a name to see the stats" slicer. YMMV.

            PointsReported[PtsDate] <= MAX(PointsReported[PtsDate]) // add the -/+ points where the date is between

                                                                                                            // the start/expire dates

            )

    )

 

 

 

 

I came at it from another direction. Maybe not very elegant, but it works. I didn't find a visual that showed what I wanted, so I made-do with a stepped line with a fill color. Otherwise, the logic is: With some redundancy, set a plus count and a minus count for every record you need counted. Then do some logic to pre-define if you're counting or subtracting based on the the date. THEN just count the pre-defined value instead of trying to do it all at once.