topic Re: Calculate sum of the difference between 2 columns from different table in DAX Commands and Tips

Calculate sum of the difference between 2 columns from different table

Anonymous — Wed, 18 May 2022 19:16:38 GMT

EDIT:
Thank you all for the help, but I just found a solution for my problem
_________________________________________________________________________________________
Hi.
I have 2 tables:

Table Team: that has 3 columns: the names of the employees, their date of admission and, if applied, the date when they left the company

Table BSC: that has 2 columns: years and months, since 2020

I want to know how long my employees stayed in my company at the different dates on the BSC table. To simplify, for now, I am not considering the date they left the company. I created a representation on excel to make it simpler to understand.

Basically for each combination of year/month in my BSC table, I create a new column in the BSC table with sum of the difference between the last day of that month and the date of admission for all my employees.
My DAX code would look like this:
Column = CALCULATE( SUMX (Team, Team[dateofadmission] - DATE( BSC[year], BSC[month], DAY(EOMONTH(DATE(BSC[year], BSC[month],1),0)) , FILTER (...) )
The problem with this formula is that I can't retrieve the columns from both of my tables. Do you have any idea of how I can calculate this?
Thank you

Re: Calculate sum of the difference between 2 columns from different table

Anonymous — Wed, 18 May 2022 17:21:52 GMT

llo

Try this

Employee calc is my name table

Re: Calculate sum of the difference between 2 columns from different table

tamerj1 — Wed, 18 May 2022 17:55:18 GMT

Hi Anonymous

why don't you just creat a table visual as a measure?

Re: Calculate sum of the difference between 2 columns from different table

Anonymous — Wed, 18 May 2022 18:26:12 GMT

I'm sorry, but can you explain better how to do that?

Re: Calculate sum of the difference between 2 columns from different table

Anonymous — Wed, 18 May 2022 18:27:41 GMT

The problem is that, in reality, that information is on 2 separate tables
I just put it like that in the excel to explain what I wanted to do

Re: Calculate sum of the difference between 2 columns from different table

tamerj1 — Wed, 18 May 2022 18:51:41 GMT

Hi Anonymous

something like this

NewMeasure = VAR CurrentMonth = SELECTEDVALUE ( BSC[month] ) VAR CurrentYear = SELECTEDVALUE ( BSC[year] ) VAR CurrentDate = DATE ( CurrentYear, CurrentMonth, DAY ( EOMONTH ( DATE ( CurrentYear, CurrentMonth, 1 ), 0 ) ) ) RETURN SUMX ( Team, VAR StartDate = Team[dateofadmission] VAR EndDate = IF ( ISBLANK ( Team[dateoftermination] ), CurrentDate, MAX ( CurrentDate, Team[dateoftermination] ) ) RETURN EndDate - StartDate )