Create Date Function. Help.

[AndradeRobson]

831 / 5.000

Resultados de tradução

star_border

Dear, good night!

I need help. I have in Power BI a complete record of employees, where I have an Admission Date column and another Dismissal Date column. I would like help to have a function to count the amount of hired in the selected month in the slicer and another function to count dismissed in the selected month in the slicer.

Example: From 01/04/2022 to 30/04/2022 there were 5 Admissions on different days within April. How to create a function to count those admitted within April?? From 01/04/2022 to 30/04/2022 there were 10 layoffs on different days within April.

How to create a function to count those fired within April, and in this column what doesn't have a date is null?? Please if you can help me, I've tried to create functions, but I couldn't.

Att. Robson

[AndradeRobson]

No problems. I apologize, I don't speak English, I use Google Translate.

Photos attached.

Summary:

I have a Table of the General Register of Employees:

Competence is the date of the file that every month will have one of April, May June......

In the same table I have the Admission column and the Dismissal column

Objective:

Make the BI in the data segmentation filter the month by competence. Example: April and another filter the Year 2022

When you click on the month and year of April 2022, the function needs to show a card called Total Dismissed in April 2022 and ignore those dismissed on 05/2022, because the Competency file is from 30/04/2022.

In the Dismissal column I have the dates of 12/04/2022 and I have the dates of 11/05/2022 and 04/05/2022

I need the dax function to do a count of how many dates from 04/2022 are in the Dismissal column, ignoring the dates from 05/2022.

Att.

Robson

[tamerj1]

@AndradeRobson 
Thank you for the briefe description. Actually I have many fiends from Brazil and I think it is time to learn some Portuguese. I believe it is too late now in Brazil and probably you'll get to be soon. 
I don't see any reason for not having the correct result out of this code. Maybe we can connect via zoom or teams. Don't worry my english is no much better than yours 🙂

[AndradeRobson]

Rectifying conversation.

The function did it correctly, it counted the total number of dismissed people in the general table, but in the dismissed column we have two dismissal dates from 04/01/2022 to 30/04/2022 and dismissals from 01/05/2022 to 31/05/2022, however the function is not separating, for example, it has to total only those fired in 04/2022.

[tamerj1]

Hi Robson,

Do you have a date table? Can you paste the names of the columns and the name of the table to use them in the code?

[AndradeRobson]

The function did it correctly, it counted the total number of dismissed people in the general table, but in the dismissed column we have two dismissal dates from 01/04/2022 to 30/04/2022 and dismissals from 01/05/2022 to 31/05/2022, however the function is not separating, for example, it has to total only those fired in 04/2022.

[AndradeRobson]

Good morning!

I can yes.

Date table: dCalendar | Column: Date
01_CADATRO_FUNCIONARIOS_GENERICO_GERAL | Column: Admission Date. | Column: Deut. Resignation

Att.

Robson

[tamerj1]

@AndradeRobson 
Please use

Total Hired =
VAR MinDate =
    MIN ( 'dCalendar'[Date] )
VAR MaxDate =
    MAX ( 'dCalendar'[Date] )
RETURN
    COUNTROWS (
        CALCULATETABLE (
            '01_CADATRO_FUNCIONARIOS_GENERICO_GERAL',
            '01_CADATRO_FUNCIONARIOS_GENERICO_GERAL'[Admission Date] >= MinDate,
            '01_CADATRO_FUNCIONARIOS_GENERICO_GERAL'[Admission Date] <= MaxDate,
            REMOVEFILTERS ( 'dCalendar' )
        )
    )

Total Resigned =
VAR MinDate =
    MIN ( 'dCalendar'[Date] )
VAR MaxDate =
    MAX ( 'dCalendar'[Date] )
RETURN
    COUNTROWS (
        CALCULATETABLE (
            '01_CADATRO_FUNCIONARIOS_GENERICO_GERAL',
            '01_CADATRO_FUNCIONARIOS_GENERICO_GERAL'[Deut. Resignation] >= MinDate,
            '01_CADATRO_FUNCIONARIOS_GENERICO_GERAL'[Deut. Resignation] <= MaxDate,
            REMOVEFILTERS ( 'dCalendar' )
        )
    )

[AndradeRobson]

Good evening!

The Function made perfect sense, but I didn't pay attention to a detail, because individually it works, but not in the general picture because I didn't anticipate the following situation.

I have a dCalendar for the following tables:

1st Table
Cadastro_Funcionario_Generico | Competence Column | Dismissal Date Column | Column:

Admission Date

2nd Table
Sheet_Analytics | Competency Column

Observation:

1st The dcalendary links Date to Column Competency Date of Cadastro_Empreenderio_Generico

2nd Cadastro_Funcionario_Generico links the registration of the employee to Folha_Analitica | Enrollment Column

The competency Date of the Cadastro_Funcionario_Generico table does not talk to the Admission Date and Demission Date, which cannot include the calendar date, is inactive and thus does not allow the Function you created to work.

I believe you need a function that forces the Date dCalendar that is inactive to link the Dismissal Date and Admission Date.

Sorry I thought it was just the data I passed that would solve.

Att.

Robson

[tamerj1]

@AndradeRobson 

Actually I don't need the relationship. I even removed the filter from the dcalendar table. What results did you get?

[AndradeRobson]

The function did it correctly, it counted the total number of dismissed people in the general table, but in the dismissal date column we have two dismissal dates from 04/01/2022 to 30/04/2022 and dismissals from 01/05/2022 to 31/05/2022 , but the function is not separating, for example, it has to total only those fired in 04/2022.

[tamerj1]

Godd morning @AndradeRobson 

I'm having some difficulties understanding what does that mean. Would you please share some screenshots perhaps with some markups? Thank you for your patience 

[AndradeRobson · Accepted solution]

Good Morning!

I got what I needed. I changed the table to be applied to the function and it worked.

Thank you very much for your support.

Att.

Robson