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

      • tamerj1's avatar
        tamerj1
        Community Champion
        4 years ago

        AndradeRobson 

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