Forum Discussion

AndradeRobson's avatar
AndradeRobson
Helper II
4 years ago
Solved

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
Need Help
  • AndradeRobson's avatar
    AndradeRobson
    4 years ago

    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

12 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    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's avatar
      AndradeRobson
      Helper II
      4 years ago

      Good morning!

       

      I can yes.

       

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

       

      Att.

       

      Robson

      • tamerj1's avatar
        tamerj1
        Community Champion
        4 years ago

        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's avatar
      AndradeRobson
      Helper II
      4 years ago

      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's avatar
    AndradeRobson
    Helper II
    4 years ago

    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.

  • AndradeRobson's avatar
    AndradeRobson
    Helper II
    4 years ago

    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's avatar
      tamerj1
      Community Champion
      4 years ago

      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 🙂