Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Measure to Calculate the Difference between two values using different logic

Hi All,

 

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

 

Report DateReport Date IndexJoinersLeavers
12/31/2024110
12/31/2024111
12/31/2024100
1/7/2025201
1/7/2025200
1/7/2025210
1/14/2025301
1/14/2025311
1/14/2025310
1/21/2025401
1/21/2025400
1/21/2025400
1/28/2025510
1/28/2025511
1/28/2025501
1/31/2025610
1/31/2025601
1/31/2025601

 

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!

Need Help
  • vicky_'s avatar
    vicky_
    1 year ago

    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

2 Replies

  • vicky_'s avatar
    vicky_
    Super User
    1 year ago

    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's avatar
    amitchandak
    Super User
    1 year ago

    Anonymous ,

    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