Selecting only the latest date between many using DAX

[Anonymous]

Hello everyone,

Happy new year ! 🎉

It's my first time asking a question here, but I am cornered, hope one of you will be able to help me !

Here is the situation : I want to create a matrix using an extraction from my HR tool. The use is to follow if my employees have had their interview with their managers every 2 years (after the hiring).

The extraction gives me all the interviews of the employees but I only want to use the latest.

I created a new metric to filter the table using this DAX expression :

__________

Date_la_plus_recente=

VAR date_recente =

MAX('Entretien professionnel 2022'[Date d'entretien])

RETURN

CALCULATE(

    date_recente,

    FILTER(

        ALL ('Entretien professionnel 2022'),

        CALCULATE(COUNT('Entretien professionnel 2022'[Nom d'utilisateur]),'Entretien professionnel 2022'[Nom d'utilisateur] = EARLIER('Entretien professionnel 2022'[Nom d'utilisateur])

        && 'Entretien professionnel 2022'[Date d'entretien]=date_recente

        )

    ))

__________________
It works, now I only see one line for each employee (when Not OK) BUT (and here is my problem), I also want to calculate the penalty tax if the interview hasn't been done on time (NOK).

The maximum tax is 3000€/employee, as you can see PBI made the sum of every line for every employee and the total is also

the same as before (so it's wrong).

I thought that I can modify it by selecting "don't resume" on the visualisation pannel :

 but this earase the "Total" line and I need it.

Power BI calculates the sum of every line of the table even if it doesn't show on my graphs. I wanted to add a new column to add a filter by "True or False" like that : 

=IF([Date d'entretien]=date_recente,TRUE,FALSE) 

But it dont work as well...

Do you have any idea of what I can do ? 

Thank you in advance !

[amitchandak]

@Anonymous ,
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

refer if these approaches can help

Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

Between Dates - Dates between
Measure way
Power BI Dax Measure- Allocate data between Range: https://youtu.be/O653vwLTUzM
https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-or-end-date/ba-p/1503785

Tables way
https://amitchandak.mediumcom/dax-get-all-dates-between-the-start-and-end-date-8f3dac4ff90b
https://amitchandak.medium.com/power-query-get-all-dates-between-the-start-and-end-date-9ad6a84cf5f2

[Anonymous]

Thank you for your answer ! 

I think I can but I don't know how to share it here, can you tell me (it's embarassing, sorry) ?

Thx !