Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
AndradeRobson
Helper II
Helper II

Create Date Function. Help.

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
1 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

View solution in original post

12 REPLIES 12
AndradeRobson
Helper II
Helper II

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.

 

Robson05 Captura de tela.jpg04 Captura de tela.jpg03 Captura de tela.jpg02 Captura de tela.jpg01 Captura de tela.jpg

 

 

@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
Helper II
Helper II

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
Super User
Super User

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?

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.

Good morning!

 

I can yes.

 

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

 

Att.

 

Robson

@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' )
        )
    )

 

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

@AndradeRobson 

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

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.

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 

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.