Sum of count distinct by year

[obuolys123]

Hello, I need to sum distinct count values (employee id's) by their start year of working. I attached photo below.

I tried:

Sum workers by year = SUMX(DISTINCT(table[employee.id]),[distinct.count.id's])

But I actualy get the same result as B column...

Tried to use CALCULATE, but still was unable to get the right result. I'm using start year basicaly from employees start date (e.g. 2001-02-12 = 2001). Does anyone have any ideas? How to get sum by year?

[Thejeswar]

Hi @obuolys123 ,

It looks like a Running Total

You can create a measure like the one below as a first step

Employee_Count = SUM(Table [employee_id])

You can create another measure by trying the below DAX

RT distinct count of employee id :=
VAR CurrentemployeeYear = SELECTEDVALUE ( Table[Start year] )
RETURN
    CALCULATE (
        [Employee_Count],
        Table[Start year] <= CurrentemployeeYear,
        ALL ( Table[Start year] )
    )

This should give you the expected result
Regards,

[obuolys123]

Hi, @Thejeswar 

Thank you for help, the issue is that I can't SUM employees ids from table because they duplicate a lot, I need DISTINCT values SUM. I tried to SUM it your way, but I get enormous number. I dont really know how to sum these distinct ids, i get the idea of your suggestion, but can't really get to it. The excel photo I showed is made up, I get Column B by just distinct count of ids in PBI Desktop. Since SUM (as i know, maybe I am wrong)  cant sumarise a measure (distinct count of ids) I can not get the result. Maybe you know what to do in this case? 

Thank you in advance

[AilleryO]

Hi,

If you use for the measure Employee count a DISTINCTCOUNT( [employeeID] ) instead of SUM, does it works ?

Employee_Count = DISTINCTCOUNT( Table [employee_id] )

Hope it helps

[obuolys123]

Hi @AilleryO 

Thank you for the help. Tried it earlier, but it did not work. 

darbuotojai_imones(dirba_nuo) = table(start_date)

Priimti pamečiui = DISTINCTCOUNT(table[employee_id])

Priimtų metai = Format(table[start_date],"yyyy")

I hope you understand this explanation. Dont really have time right now to share all information and to give you pbix, I would need time to minimize the data.

[AilleryO · Accepted solution]

Your SELECTEDVALUE should not be on  column [priimtu metai], the one you're using in your table ?

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[obuolys123]

Hello, @Ashish_Mathur 

I am sorry but I can not do that, since it has confidential information in it which I am not allowed to share...