DAX: Calculate active employee count by Carry forward previous calculated value to next row

[Anonymous]

Hello All,

I have a requirement to calculate the active emplopyees using dax and the data follows as below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZBLDsUgCEX34rhNAGs/w7KNpvvfxlP6IBhIxMmJl+t5ngK49kNAUJZy90HqV3mXD9GEwKPq0SajaJvQYGiseUa1X4exPRQhY4dnbWQ2Y2fIvIxdU5fmfoDgUSVfEzFEnsYoVKm2zt7x30pUybIoM8kSlZpkrZGY1FWJSE1MPLJoSjXKstyi9YgWtUYiUWskDgfaP4XvDw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Org ID" = _t, #"New Joinees" = _t, Exits = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Org ID", type text}, {"New Joinees", Int64.Type}, {"Exits", Int64.Type}})
in
    #"Changed Type"

every month for each org, i need to calculate the total active employees as Newjoinees-exits.

But while calculating the new joinees i need to consider the previous month count +current month new joinees-exits of this month.

Output and the formula mentioned as below image.

I am able to get the running count and do the calculation of running count new joinees - exits but unable to carry forward that value to the next row to calculate the same.

Can any one please help me on this.

Thanks in advance.

Mohan V.

[v-jingzhang · Accepted solution]

Hi @Anonymous 

You can create a new column with below DAX. Replace Query1 with your table name. 

Column = 
SUMX (
    FILTER (
        Query1,
        Query1[Org ID] = EARLIER ( Query1[Org ID] )
            && Query1[Date] <= EARLIER ( Query1[Date] )
    ),
    Query1[New Joinees] - Query1[Exits]
)

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[amitchandak]

@Anonymous , new measure or column in DAX

new measure =
calculate(sum(Table[New joinee]) - sum(Table[exits]), filter(allselected(Table), Table[Org ID] = max(Table[Org ID]) && Table[Date] <= max(Table[Date])))

New column
sumx( filter(allselected(Table), Table[Org ID] = max(Table[Org ID]) && Table[Date] <= max(Table[Date])), [New joinee] - [exits] +0)