cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

## Accumulated account

Good morning

I need to make a graph that shows the number of active personnel that the company has had over the last 5 years. The table I have has the name of the employee, the date of entry and the date of retirement, the latter is empty in cases where people are still working within the company.

Example of the data:

 identification Number FECING FECRETIRO 102437 CRLZL 2017-03-07 2017-12-31 764383 PLLXN 2019-07-30 2019-07-30 915750 JNFRT 2021-01-14 91287345 MRPLR 2020-08-24 2022-04-19 9151387 JLNND 2021-03-15 2H916170 NCLLD 2022-10-06 2023-04-04 AB916 JRGML 2012-05-14 Z91645 NLSNJ 2021-01-04 2022-04-18 91732740 LRVNS 2021-04-21 N9188207 MRFRN 2020-08-24 2022-04-15 191057050 NMRRM 2021-02-24

I have made some measurements using the count function, but what I manage to get is the active staff to date or the difference of the staff that entered and left in a year.

I appreciate your cooperation.

1 ACCEPTED SOLUTION
Super User

Hi @nana014 / @Syndicate_Admin , if I understood correctly I had a similar scenario where I needed to compute the open accounts on a time period and I achieved it with following:

``````# Active Personnel =
//Use a Date Dimension (with Start and End date of month) filtered for the last 5 years in my case called vdDate
var _helperdate =
SUMMARIZE(
FILTER(
vdDate,
vdDate[Year] >= YEAR( TODAY() ) - 5
),
vdDate[Year Month Code],
vdDate[Year Month],
vdDate[Start of Month],
vdDate[End of Month]
)

//Crossjoin with your Fact Table to create a temporary table with all possible outcomes ( I am using a fake name for your table "YourFactTable")
var _firsttable =
SUMMARIZE(
CROSSJOIN(
YourFactTable,
_helperdate
),
YourFactTable[identification],
YourFactTable[number],
YourFactTable[FECING],
YourFactTable[FECRETIRO]
)

//Filter the temporary table for the interval of dates that each user was active
var _filtertable =
FILTER(
_firsttable,
YourFactTable[FECING] <= vdDate[Start of Month]
&& YourFactTable[FECRETIRO] > vdDate[Start of Month] &&
YourFactTable[FECRETIRO] >= vdDate[End of Month]
&& YourFactTable[FECING] < vdDate[End of Month]
)

return
COUNTROWS( DISTINCT( SELECTCOLUMNS( _filtertable, "Active Personnel", [identification] ) ) )``````

If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudoe 👍
Thanks!

You can also check out my LinkedIn!

Best regards,
Gonçalo Geraldes

4 REPLIES 4

Hello

The problem is that you are calculating a table with the SUMMARIZE function but in a Measure.
The example I gave is to calculate it as a calculated table and not as a Measure.
If you want it as a completely independent function it is different and I would need more information about the data model to try to see if you can.

Best regards

Thank you very much, I see in the example that you present that the expected result is obtained, but when trying to replicate the code it generates an error: "The expression refers to several columns. You cannot convert multiple columns to a scalar value." I have not been able to identify what difference there is. I see that you attached a file, however, I do not find there a measure similar to the one you indicated.

I appreciate your collaboration.

Hi Nano,

You can achieve this with a calculated table that can evaluate per month how many employees there are active.
You will need a table with dates, start date of month and end of month also to be able to make the calculation.
Here you find an example of how to do it: Average Calculation per seller.pbix

Did I answer your question? Mark my post as a solution!

Super User

Hi @nana014 / @Syndicate_Admin , if I understood correctly I had a similar scenario where I needed to compute the open accounts on a time period and I achieved it with following:

``````# Active Personnel =
//Use a Date Dimension (with Start and End date of month) filtered for the last 5 years in my case called vdDate
var _helperdate =
SUMMARIZE(
FILTER(
vdDate,
vdDate[Year] >= YEAR( TODAY() ) - 5
),
vdDate[Year Month Code],
vdDate[Year Month],
vdDate[Start of Month],
vdDate[End of Month]
)

//Crossjoin with your Fact Table to create a temporary table with all possible outcomes ( I am using a fake name for your table "YourFactTable")
var _firsttable =
SUMMARIZE(
CROSSJOIN(
YourFactTable,
_helperdate
),
YourFactTable[identification],
YourFactTable[number],
YourFactTable[FECING],
YourFactTable[FECRETIRO]
)

//Filter the temporary table for the interval of dates that each user was active
var _filtertable =
FILTER(
_firsttable,
YourFactTable[FECING] <= vdDate[Start of Month]
&& YourFactTable[FECRETIRO] > vdDate[Start of Month] &&
YourFactTable[FECRETIRO] >= vdDate[End of Month]
&& YourFactTable[FECING] < vdDate[End of Month]
)

return
COUNTROWS( DISTINCT( SELECTCOLUMNS( _filtertable, "Active Personnel", [identification] ) ) )``````

If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudoe 👍
Thanks!

You can also check out my LinkedIn!

Best regards,
Gonçalo Geraldes

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors