Measure to Calculate the Difference between two values using different logic

[Twizxup]

Hi All,

I have the below table that tracks joiners & leavers in a given week:

Report Date	Report Date Index	Joiners	Leavers
12/31/2024	1	1	0
12/31/2024	1	1	1
12/31/2024	1	0	0
1/7/2025	2	0	1
1/7/2025	2	0	0
1/7/2025	2	1	0
1/14/2025	3	0	1
1/14/2025	3	1	1
1/14/2025	3	1	0
1/21/2025	4	0	1
1/21/2025	4	0	0
1/21/2025	4	0	0
1/28/2025	5	1	0
1/28/2025	5	1	1
1/28/2025	5	0	1
1/31/2025	6	1	0
1/31/2025	6	0	1
1/31/2025	6	0	1

I need to calculate the number of net joiners/leavers for a given month based on the following logic:

Joiners = Sum of Joiners in a given month based on the month of the Report Date

Leavers = Sum of Leavers in a given month where the Report Date Index is shifted forward by 1 (i.e. January Leavers = sum of Leavers from 12/31/2024 - 1/28/2025, and February Leavers would start at 1/31/2025)

Using this logic, the returned values would be:

December Joiners = 2

December Leavers = 0 (nothing in the data would be considered December Leavers)

December Net = 2

January Joiners = 6

January Leavers = 7

January Net = -1

Any help would be hugely appreciated! Thank you in advance!

[vicky_ · Accepted solution]

I've created a date table to grab the month:

And then created two measures:

_Joiners = SUM('Table'[Joiners])

_Leavers = 
var indexes = SELECTCOLUMNS(ADDCOLUMNS(VALUES('Table'[Report Date Index]), "index minus 1", 'Table'[Report Date Index] - 1), [index minus 1]) 
RETURN CALCULATE(SUM('Table'[Joiners]), 'Table'[Report Date Index] in indexes, REMOVEFILTERS('Date Table'))

which gives this result:

You can then create a third measure to grab the net joiners:

_Net Joiners = [_Joiners] - [_Leavers]

Hope this helps

[amitchandak]

@Twizxup ,

Join report date with date of date table and then have measure like

calculate(Sum(Table[Leavers]), dateadd(Date[Date], 1, day))

Use month year, qtr or any other period from the date table in visual and calculations

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw