Datediff Probleam

[rafamorim87]

Goodnight

I would like some help, I have a database that has employee names and their hire dates. I created the formula to calculate company time based on the date entered in the filter.
I created the formula in the image, but when I put it on the graph, it is not showing the values. See image below Could anyone give me some more help, please?

[RossEdwards · Accepted solution]

Change your vLastDate to:

CALCULATE(LASTDATE(dimCalendario[Data]), ALLSELECTED(dimCalendario))

The problem is that your date context in your table means "LastDate" is currently giving you the last date of that year, which will always be a datediff of 0.

[v-yilong-msft]

Hi @rafamorim87 ,

As you show the DAX code on the image, I noticed that you are using the DATEDIFF function, but it is used to return the number of boundaries of the interval between two dates. Here it is using YEAR, so it will also only show 0 between two neighboring years and the final Total is 4.

You can check this documentation below: DATEDIFF function (DAX) - DAX | Microsoft Learn

Best Regards

Yilong Zhou

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

[RossEdwards · Accepted solution]

Change your vLastDate to:

CALCULATE(LASTDATE(dimCalendario[Data]), ALLSELECTED(dimCalendario))

The problem is that your date context in your table means "LastDate" is currently giving you the last date of that year, which will always be a datediff of 0.

[rafamorim87]

Thank you very much. I managed to solve it. Now I need just one more help, if I can. 1 - I would like to know how I can group the number of employees according to this measure I created. 2- How can I calculate the average of this measurement I created Thank you again in advance

[RossEdwards · Accepted solution]

Best practise is to model your data in a Star Schema.  I would suggest adding to your model a "Person" table, that contains 1 row per person. Then use a unique ID to create a relationship between your person table and the data table.  When building your visuals, use the Person table's fields on your report. When using the measure, this will constrain your measure to run once for each person.

To creature an average, we'll use the Average iterator function.  I'll assume you have a person table for my example:

Average Example = AVERAGEX(
    VALUES('People'[Person Name]),
    [Tempo de Empresa]
)