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 col...
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

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

        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'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.