Number of active placements card based on start and end date

[Alizee]

Hello,

I am currently building a report where I need to show the number of active placements we had any given month.

I have tables that contains these columns:

Placements                        KAM                              Calendar

Placement ID                     Kam.name                     Date (month)

DateBegin_month

DateEnd_month

I need the KAM name to match the placements to the right person in the other slicer I have and the calendar[Date (month)] is the value that goes into the month slicer.

The month slicer and the card slicer

To do so, I wanted to create a formula so that a placement is deemed active when the month selected through the slicer is between the start date and the end date of the placement but so far, none of my formulas worked and doesn't show the right value at all. 

Does anyone know how I could make it work? 

Thank you!

[v-zhangti · Accepted solution]

Hi, @Alizee 

Do you have a simple PBIX file available for testing? Something still doesn't feel right in my simulation of your data.

For the filtering of KAM name, you can refer to the following methods.

Measure name = IF(SELECTEDVALUE(KAM[Kam.name])=SELECTEDVALUE(Placements[Owner name]),1,0)

Is this the result you want to achieve for the first filter? If the method I provided above can't solve your problem, what's your expected result? Could you please provide more details for it?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Alizee]

Hello,

Thank you for your reply, there was actually an issue with the date field and its relationship in the model to the other fields. I was able to get the right value once this was corrected and your answer helped me a lot for the KAM filtering, thank you!

[amitchandak]

@Alizee ,  Please check one of the two should help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-o...