Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
RemiAnthonise
Helper V
Helper V

Filter data on projectdate

Hi guys,

 

I have a problem and I can't solve it.

I have a list of employees and I want to filter them:

 

- on employment: are they employed in the month I pick with my slicer?

Startdate has to be in or before the selected month and enddate has to be in or after the selected month. So, the employment has to be active in that month

 

- I have a table with journal lines for every month. It contains all the hours my employees have worked. If the employee is in that table, I don't want to see it in my list for that selected month, based on my slicer. So: if someone has worked in march, so he has journal lines with worked hours in that month, and I select 'march' with my slicer I don't want to see the employee. I only want to see employees without transactions in the selected month.

 

I somehow have to create some measures:

- if the projectdate in the JournalTransactions isn't BLANK(), I don't want to see it

- it has to compare the EmploymentStartDate and EmploymenEndDate with the selected month in the slicer.
In the image below you'll see 2 marked employees. If I pick 'march' in my slicer I don't want to see them because they don't work for me anymore.

filtered empl.jpg

 

I hope everything is clear. If not, please feel free to ask.

You can find my sample here. All data is anonymous, the names are random generated.

5 REPLIES 5
RemiAnthonise
Helper V
Helper V

@Nishantjain  @v-danhe-msft 

 

We are almost there. I did merge all the tables and I filtered on ProjectDate = (blank), because this gives me all the employees without an projectdate, which means there are no transactions with worked hours. But, next problem: if all the projectdates are blank and my relationship between this table is based on date'date' and baseworkers'projectdate', I get 0 records when I use my slicer because it can't find matching records. So I need to do something like a reverse slicer: if I pick 'January' from the slicer, it must show me all the names without transactions in that month, so all empty rows unequal selected month. How can I fix this?

Below image explains this. You'll see the red record I don't want to see in January and you'll see the blue record I don't want to see in March, because of the Start- and EndDate.

Test.jpg

 

Now, I want to use the measure from Nishantjai  :

Active Employee with no transaction =
VAR firstDayOfTheMonth = EOMONTH(max('Date'[Date]);-1)+1
VAR LastDayOfTheMonth = EOMONTH(max('Date'[Date]);0)
return
if(ISFILTERED('Date'[MonthNameLong]);
if(SUMX(FILTER(BaseWorkers;BaseWorkers[EmploymentStartDate]<=LastDayOfTheMonth && BaseWorkers[EmploymentEndDate]>=firstDayOfTheMonth);CALCULATE(COUNTROWS(BaseWorkers); BaseWorkers[ResourceId] = EARLIER(BaseWorkers[PersonnelNumber])))>0
;"Active";BLANK());
BLANK())

I've edited it because now all my data is in the table BaseWorkers. If I add the measure to my table on my report, it doesn't give me any data. I understand what Nishantjain means with the measure but is it right? I doubt about this because of this part:

 

VAR firstDayOfTheMonth = EOMONTH(max('Date'[Date]);-1)+1
VAR LastDayOfTheMonth = EOMONTH(max('Date'[Date]);0)

The max date in the 'Date'[Date] is today because this table is based on max = TODAY().

 

I think it should be something like this, but obviously this isn't workin:

 

VAR firstDayOfTheMonth = EOMONTH(min(ISFILTERED('Date'[MonthNameLong]);0)
VAR LastDayOfTheMonth = EOMONTH(max(ISFILTERED('Date'[MonthNameLong]);0)

 

Here you can find an updated sample.

Now I am thinking about inserting rows in my table: if there isn't a row between the min-date and max-date of the selected row, create a record in the ProjectDate-row with "no data in this month" and filter on this. But I don't know if this is possible.

 

@Greg_Deckler I saw some solutions from your side, solving similar issues. Maybe you have an idea about above problems.

I expect my report to be finished and correct if this measure works, so it would be nice to have this solved.

Because we needed this report before April 1st, we've decided to solve this in our ERP software Dynamics 365. This topic can be closed / deleted.

 

Thanks.

RemiAnthonise
Helper V
Helper V

Hi @Nishantjain  , @v-danhe-msft  ,

 

I will try to make it clear: 

 

If an employee (table 'Employees') works, his hours will be registered in journals (table 'JournalTransactions') in our administration. Every month, we have to check if the employee filled in all his worked hours for that month. I want to create an overview where you can filter on a month and you'll see the employees without transactions in the JournalTransactions-table. So, normally you'll see data that is really in that table, now I want to see the opposite: data that isn't in that table. 

So, if we use the Employees-table as a basis, we have to search in the JournalTransactions if that table has a record for that user. If it has, I don't want to see the worker in my list. If it hasn't, I want to see the worker. 

 

To make it a bit more complicated, I have a table with employments. It is possible that an employee stops working for us, in that case he doesn't have records anymore in the JournalTransactions because he won't work hours anymore. 

 

Summarizing:

The report is based on the Employee-table. It has to check if there are records for that worker in the JournalTransactions and it has to check if the employee has an active employment in the Employment-table. 

 

This is what I expect:

You'll see the red rows in January, I don't want to see these because you see transactions from the JournalTransactions. The green rows must be visible because they don't have these transactions.

 

You'll see the red row in February, there are no transactions but the EmploymentEndDate is in January. The green rows must be visible because it doesn't have a transaction and the EmploymentEndDate is later.

show no show.jpg

 

Now, all my data is in 3 separate tables. I can merge these 3 tables together based on PersonnelNumber to create one table with all the data. I don't know if this is the right way. In the image above, I did this but this is the most ideal case. When I merge the tables it's possible I get 50 records (and counting, every month this will grow because my employees book hours every day so this table will grow quick).


What do you think about this? Is everything clear?

Nishantjain
Continued Contributor
Continued Contributor

@RemiAnthonise 

 

try the measure below

Active Employee with no transaction= 
VAR firstDayOfTheMonth = EOMONTH(max('Date'[Date]),-1)+1
VAR LastDayOfTheMonth = EOMONTH(max('Date'[Date]),0)
return
if(ISFILTERED('Date'[MonthNameLong]),
    if(SUMX(FILTER(Employments,Employments[EmploymentStartDate]<=LastDayOfTheMonth && Employments[EmploymentEndDate]>=firstDayOfTheMonth),CALCULATE(COUNTROWS(JournalTransactions), JournalTransactions[ResourceId] = EARLIER(Employments[PersonnelNumber])))>0
    ,"Active",BLANK()),
    BLANK())

Thanks

Nishant

v-danhe-msft
Employee
Employee

Hi @RemiAnthonise ,

From your describtion, what is your desired result? Which measure do you want to get and how could the measure work? Could you please post your desired result if possible?

 

Regards,
Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.