Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Syndicate_Admin
Administrator
Administrator

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 NumberFECINGFECRETIRO
102437CRLZL2017-03-072017-12-31
764383PLLXN2019-07-302019-07-30
915750JNFRT2021-01-14
91287345MRPLR2020-08-242022-04-19
9151387JLNND2021-03-15
2H916170NCLLD2022-10-062023-04-04
AB916JRGML2012-05-14
Z91645NLSNJ2021-01-042022-04-18
91732740LRVNS2021-04-21
N9188207MRFRN2020-08-242022-04-15
191057050NMRRM2021-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
goncalogeraldes
Super User
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] ) ) )

 

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

View solution in original post

4 REPLIES 4
Syndicate_Admin
Administrator
Administrator

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
Administrator
Administrator

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.

nana014_0-1688758584876.png

nana014_1-1688758681492.png

Syndicate_Admin
Administrator
Administrator

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

AlanFredes_0-1688749662515.png

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




goncalogeraldes
Super User
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] ) ) )

 

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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