Filter by two dates (different areas)

[Anonymous]

Hey there,

Is there any filter to perform calculations based on two dates, namely start and end date? In this example, I've assigned dates to employees and I aim to calculate the sum of the Target measure, but filtering by the Area slicer, (in this case) Peter shouldn't be there - both in the chart and in the card that displays “3”, because he has never been assigned to such Area.

I know there should be a relationship between Info_Employee and Fact_tbl, but I didn't find a way to do so. I figured that it should be done throught the USERELATIONSHIP() function, however I got stucked there, because I don't know how to do the further steps to make such formula to count people who was part of this area in the filtered period of time.

To sum my doubt up, what I'd like to do is firstly count how many people were part of this area in a given period of time, and then calculate the sum of those who have been assigned to the filtered area.

The count formula is 

DISTINCTCOUNTNOBLANK(Fact_tbl[Employee])

 and the sum is just 

Thanks in advance.

[amitchandak]

@Anonymous , Can refer to this example

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

also check -  Average of Rolling, Average of Snapshots: https://youtu.be/_pZRdLAJxxA

[Anonymous]

hey buddy

Ive tried what you suggested, and even following your youtube video I couldn't make it work. However I figured a way to work this around by using the LOOKUPVALUE, but once again I got stuck in a problem: is it possible to use not the first but instead the Nth value in a list? For instance, in this link i've previously provided I would like to get not the first, but the second result contained in the Info_Employee table.

Area = LOOKUPVALUE(Info_Employee[Area] , Info_Employee[Employee] , Dim_Employee[Employee])

A table of multiple values was supplied where a single value was expected.

This hypotetical scenario should bring Peter in the “Finances” area (in my real case scenario I'll make this work through the week in the Fact table, and the condition is going to be in what area the person is in the filtered date range in a table like the one shown above). Having this in mind, do you know a way to make the LOOKUPVALUE function work with more than one register?

Once again, thanks in advance 🤝

[Anonymous]

Thanks for your response @amitchandak . As soon as I am able to, I'll definitely check this out.