Accumulated account

[Syndicate_Admin]

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.

[Syndicate_Admin]

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

[Syndicate_Admin]

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.

[Syndicate_Admin]

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!

[goncalogeraldes · Accepted solution]

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

Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
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