Forum Discussion

Chrisg07's avatar
Chrisg07
Frequent Visitor
3 years ago
Solved

filtered data showing wrong result

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
Need Help
  • Chrisg07's avatar
    Chrisg07
    3 years ago

    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

5 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 years ago

    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.