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 Date Report Date Index Joiners Leavers 12/31/2024 1 1 0 12/31/2024 1 1 1 12/31/2024 ...
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

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