filtered data showing wrong result

[Chrisg07]

Hello All,

I have a data file with 64 Records contain users data. I have trying to filter the data to create a new table with the filtered data. I am trying to count the number of users that we active at one point in the month of April.  I want to count them if the were active for at least one day in April in there position.   Here is the how I wrote the code to create the new table

Active Users  = SUMMARIZE(

                FILTER(Roster,

                                 'Roster'[PositionStartDate] <= Date(2023,4,30)                    

                                  && (ISBLANK(Roster[TerminationDate]) || Roster[TerminationDate] >= Date(2023,4,1))

                                  && (ISBLANK(Roster[PositionEndDate]) || Roster[PositionEndDate] >= Date(2023,4,1))),
                        Roster[ ID], Roster[Division], Roster[Region], Roster[Role]
      )

I am expecting 40 users, but it keeps coming up with 34. I have looked at the records that are being left out and nothing seem to point to what they are not getting picked up.  

Any help will be greatly appreciated.

here the URL to the files: PowerBI

[Ashish_Mathur]

Hi,

My thought is to split each row into one row for each month between the start date and the earlier of (Termination date or End date).  This will lead to a number of rows being generated.  If you are amenable to my approach then i can try to implement it.  Let me know. 

[Chrisg07]

Yes, please feel free to try your approach. I would though it would be pretty simple. I guess not. Thank you so much for your prompt response.

[Ashish_Mathur]

The answer should be 34 (not 40).  Please find attached my solution file.

Hope this helps.

[Chrisg07 · Accepted solution]

Hi Mathur! thank you again for your help on this. I went back and check the data again and the query used to pull the data was not correct. You are correct that it is 34.  I should have looked a litle closer at that. 

Everything is working fine now.

Thank you so much again

[Ashish_Mathur]

You are welcome.