Forum Discussion

PatrickByGecko's avatar
PatrickByGecko
Icon for Helper V rankHelper V
6 years ago
Solved

Creating a new table from 2 others with a calculation

Hello

I want to create a new table from two others.

I have one table of DATES with one column Dates (from 1/1/2019 to 31/12/2050 for instance) and an other one with WORKERS information (Name, Sex, DateEntry for instance)

How can I manage to have a calculatetable (?) of 3 columns with :

  • all dates from Dates (1/1/2019 to 31/12/2050)
  • for each Date the number of  WORKERS whose sex= male whose DateEntry is <= currentdate
  • for each Date the number of WORKERS whose sex=female whose DateEntry is <= currentdate

Thank you very much

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    PatrickByGecko  - You could create the following Calculated Table in DAX:

    New = 
ADDCOLUMNS(Dates, 
    "Nbr Male", 
    COUNTROWS(
        FILTER(
            ALL(Workers),
            Workers[DateEntry] <= Dates[Date] && Workers[Sex] = "male"
        )
    ),
   "Nbr Female", 
    COUNTROWS(
        FILTER(
            ALL(Workers),
            Workers[DateEntry] <= Dates[Date] && Workers[Sex] = "female"
        )
    )
)

     

14 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    PatrickByGecko  - Try the following:

    1. Create a relationship between the 2 tables, if there is not yet one.

    2. Create a measure for Males and a measure for Females. For example:

    Females = 
var _maxdate = max(Dates[Date])
return COUNTROWS(
    FILTER(
        ALL(Workers),
        Workers[DateEntry] <= _maxdate && Workers[Sex] = "female"
    )
)

    3. Create a table visual, and add the date from the Dates table, along with your 2 new measures.

    I hope this helps. If it does, please Mark as a solution.
    I also appreciate Kudos.
    • PatrickByGecko's avatar
      PatrickByGecko
      Icon for Helper V rankHelper V
      6 years ago

      Sorry but It is not what I want.  Here is more details

       Table DATE

      • 1/1/2019
      • 2/1/2019
      • 3/1/2019
      • etc..

      Table WORKER (Name, Sex, DateEntry)

      • JOHN; MALE; 1/1/2019
      • BOB; MALE; 3/1/2019
      • JANE; 2/1/2019
      • HUGH; 3/1/2019

      Table NEW (Date, Nbr Male, Nbr Female)

      • 1/1/2019; 1;0
      • 2/1/2019; 1;1
      • 3/1/2019; 3;1
      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        PatrickByGecko  - You could create the following Calculated Table in DAX:

        New = 
ADDCOLUMNS(Dates, 
    "Nbr Male", 
    COUNTROWS(
        FILTER(
            ALL(Workers),
            Workers[DateEntry] <= Dates[Date] && Workers[Sex] = "male"
        )
    ),
   "Nbr Female", 
    COUNTROWS(
        FILTER(
            ALL(Workers),
            Workers[DateEntry] <= Dates[Date] && Workers[Sex] = "female"
        )
    )
)