DAX: Carry forward previous calculated value at yearly and organization level with filters

[Anonymous]

I have a table as below image where I have data of New joinees and exits of employees at orgonization level for every months for multiple years.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZJBDoQwCAD/4tlNCrXqHrc8w/j/b2ythYBgsnqZSKezHMeU4NN+mDBN8/RrD2B7Ted8IzQoaZQ1WvrDaDHoYiCsaIa5vTZhqxNBYZtm5ZpZhO1u5lfY17gUdQNIGmXUmgBu5C4MnUqW4+S7Oqr4lLUfFJWsfVRYsrJGUJKPCkLyxKBj7ZnCjP2wuKJ4+IqsEURkjaDhhdY4IQ0EviENw+IjMso+IvUvrqGuIQ3DoCF18bAhvTek8S+jb0i83eAj0ntE6nrqYoDPUo+IoC6GNiKE9lkjuwGLQXoD7ogQyd8RtcZuI0Iovz8R2IjasNiGGmXbkJHZAMCnYduA8w8=", 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"

now as per below image, when i filter the org values from slicer it should give the output of active consultants as below image where it should give the active consultants for example:

for

JAN-2020 --> previous month active emp count + new joinees-exits = active consultants

6-1 = 5

FEB-2020 --> Till Jan Active consultants count+new joinees- exits

5+2-5=2

I am able to get it by below dax measure query

Active Consultants = 
calculate(sum('Table'[New Joinees]) - sum('Table'[exits]), filter(allselected('Table'), 'Table'[Org ID] = max('Table'[Org ID]) && 'Table'[Date] <= max('Table'[Date])))

But when i filter at Year slicer as 2021, the values are not getting carry forward it is doing the cumulative sum for that year only.

where as, as per our requirement, while calculating active consultants, even after filtering the data at org level and also at year or month level, the previous months active consultants values should be carry forward to the next year or next month

as below image.

At months level filters with curret dax,

Required output,

As i am going to show case the same measure into card visuals as well. so it should work with card visuals as well.

At year level - it should give the last months active consultants value.

At month level - it should give the selected months active consultants value.

Any help or suggestions how can i solve this.

Thanks,

Mohan V.

[v-chenwuz-msft · Accepted solution]

Hi @Anonymous ,

I recommend you create a column to get the result rather than measure. As you description, the cumulative sum won't change when you do some change on slicer.

cumulative sum =
CALCULATE (
    SUM ( 'Table'[New Joinees] ) - SUM ( 'Table'[Exits] ),
    FILTER (
        'Table',
        [Org ID] = EARLIER ( 'Table'[Org ID] )
            && [Date] <= EARLIER ( 'Table'[Date] )
    )
)

But if you need measure to do that, please try this expression, replace the ALLSELECTED with ALL.

Active Consultants = CALCULATE ( SUM ( 'Table'[New Joinees] ) - SUM ( 'Table'[exits] ), FILTER ( ALL ( 'Table' ), 'Table'[Org ID] = MAX ( 'Table'[Org ID] ) && 'Table'[Date] <= MAX ( 'Table'[Date] ) ) )

Result:

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[johnt75]

Try

Active Consultants = 
var maxDate = MAX( 'Table'[Date] )
return CALCULATE( SUMX('Table', 'Table'[New Joinees] - 'Table'[Exits] ), 
    REMOVEFILTERS('Table'[Date]),
    'Table'[Date] <= maxDate
)

You need to remove the filters on date so that you can include everything before the currently selected date, not just rows which are in that time period

[Anonymous]

Hi @johnt75 ,

Thanks for the reply.

much appreciate it.

I did change the measure formula but it is not working as expected.

When i do filter the year values or month values, the previous month or year active consultant values should be inlcuded in current month or year calculations as below image.

**Expected Output when we filter year Slicer values**

But, as per your measure, it is still the same as earlier.

**Expected Output when we filter Year and Month Slicer values**

Please help.

Thanks,

Mohan V.

[johnt75]

Where are the Year and Month columns coming from? You need to use REMOVEFILTERS on those as well

[Anonymous]

Hi @johnt75 , Both Year and Month slicers are coming from same table. I took hierarchial Date slicer and from that, removed Qtr, Month, Day for Year slicer and For Months Slicer, removed Year, Qtr, Day from hierarchy.

[johnt75]

Right, I understand now. The filters are actually being applied to a local date table which Power BI is creating automatically. There are a couple of options. If you only want to group the results by organization then you can replace the REMOVEFILTERS line with  ALLEXCEPT('Table', 'Table'[Org ID]). If you're going to want to group or slice by other columns as well then you'd need to create a date table, link that to your current table and use slicers from the new date table rather than the existing table.