Creating a new table from 2 others with a calculation

[PatrickByGecko]

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

[Anonymous]

@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]

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 · Accepted solution]

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

[PatrickByGecko]

Thanks a lot.

I have a last question.

I count now only when the DATE[date] match with the WORKER[DateEntry] so as to have the exact entries for each days (and not the sum of entries until each days). I tried and it worked fine.

But my table DATES begin at 1/1/2019 and somme WORKERS[DateEntry] is from 2009, 2010 etc.. ( I do not want to change my table DATES)

Is it possible to "put somewhere a if" so as to run different only when the current date is 1/1/2019 of my table  DATE this way =>

New = 
ADDCOLUMNS(Dates, 
    "Nbr Male", 
    COUNTROWS(
        FILTER(
            ALL(Workers),
            if(Dates[date]=value("1/1/2019"); Workers[DateEntry] <= Dates[Date] && Workers[Sex] = "male"; Workers[DateEntry] = Dates[Date] && Workers[Sex] = "male")

Because that does not work.

[Anonymous]

@PatrickByGecko  - Try this one:

New 2 = 
var _CalendarBegin = DATE(2019,1,1)
return ADDCOLUMNS(Dates, 
    "Nbr Male", 
    COUNTROWS(
        FILTER(
            ALL(Workers),
            Workers[Sex] = "male" && (Workers[DateEntry] = Dates[Date] || (Dates[Date] = _CalendarBegin && Workers[DateEntry] < _CalendarBegin))
        )
    ),
    "Nbr Female", 
    COUNTROWS(
        FILTER(
            ALL(Workers),
            Workers[Sex] = "female" && (Workers[DateEntry] = Dates[Date] || (Dates[Date] = _CalendarBegin && Workers[DateEntry] < _CalendarBegin))
        )
    )
)

[PatrickByGecko]

As you can see,  with this script I loose the first range of Dates (1/1/2019) and all counters are empty (have a look at the very bottom of this screen).

[Anonymous]

@PatrickByGecko  - I think there is a problem with "ors" (||) and "ands" (&&). Please use the parentheses as they were in the measure I sent. Mixing "Ands" and "Ors" can produce unpredictable/problmatic logic. 

The logic is:

Category = "<Some Category>"

AND

(

  Worker Date = Date

  OR

  (

    Worker Date is Prior to Begin Date

    AND

    Date = Begin Date

  )

)

[PatrickByGecko]

I did put some "(" the best I can, but only the first row is completed I mean when date=_DateBegin.

[Anonymous]

@PatrickByGecko  - Can you please check some particular dates that are known to have data and let me know whether they have data for the Measure?

[PatrickByGecko]

Yes there is data as you can see. The problem is somewhere else because at this time I get no value at all for each counter...

😞

There is no value in the counters even when there is no test on "category"..

[Anonymous]

@PatrickByGecko  - Would it be possible to share your pbix file?

[PatrickByGecko]

Of course. I did an extract and the effect is the same.

Here it is => https://we.tl/t-9QoLPrU5KF

[Anonymous]

@PatrickByGecko  - The problem is the column [DATE ENTREEE] contains Date + Time information. Therefore, it does not contain exact matches with the date table, which is Date only. 

You could create a new column like this:

Date2 = INT(Collaborateurs2[DATE ENTREEE])

Then use the new column in the Relationship and in the DAX, instead of [DATE ENTREEE]

I hope this helps. If it does, please Mark as a solution.
I also appreciate Kudos.

[PatrickByGecko]

It is working Thanks a lot Natel.

Patrick.