Current Month vs Previous Month - New Entries and Outs (not only variance)

[lcfaria]

Hey guys, hope you're doing well and healthy.

I have a table with data from several months where I need to calculate how many new items came in for each ID and the quantities that came out.
Table below:

Expected Result:

9 entries / 11 outs

I created the measures below, but it doesn't bring the expected result.

These measures below result in 20 entries and 0 outs.
I need help solving this problem.

CurrentMonth =
VAR __LastMonth = MAX(Sheet1[year_month_id])
VAR __CM =
CALCULATE(
SUM(Sheet1[qty_items]),
Table[year_month_id] = __LastMonth
)
RETURN
__CM

PreviousMonth =
CALCULATE(
[CurrentMonth],
DATEADD(Table[date], -1, MONTH)
)

NewEntries =
VAR __Table =
ADDCOLUMNS(
VALUES(Sheet1[id]),
"CM", [CurrentMonth],
"PM", [PreviousMonth],
"MonthVariance", [CurrentMonth] - [PreviousMonth]
)
VAR __NewEntries =
SUMX(
FILTER(
__Table,
[MonthVariance] > 0
),
[MonthVariance]
)
RETURN
__NewEntries

Outs =
VAR __Sheet1 =
ADDCOLUMNS(
VALUES(Sheet1[id]),
"CM", [CurrentMonth],
"PM", [PreviousMonth],
"MonthVariance", [CurrentMonth] - [PreviousMonth]
)
VAR __NewEntries =
SUMX(
FILTER(
__Table,
[MonthVariance] < 0
),
[MonthVariance]
)
RETURN
__NewEntries

[v-kelly-msft · Accepted solution]

Hi @lcfaria ，

First create a slicer table as below:(thus you could select any date as the current date)

Slicer table = VALUES('Table'[Date])

Then Create measures as below:

variance = 
var _currentmonth=SELECTEDVALUE('Slicer table'[Date])
var _previousmonth=CALCULATE(MAX('Slicer table'[Date]),DATEADD('Slicer table'[Date],-1,MONTH))
var _t_pervious=CALCULATE(SUM('Table'[item_qty]),FILTER(ALL('Table'),'Table'[id]=MAX('Table'[id])&&'Table'[Date]=_previousmonth))
var _t_current=CALCULATE(SUM('Table'[item_qty]),FILTER(ALL('Table'),'Table'[id]=MAX('Table'[id])&&'Table'[Date]=_currentmonth))
Return
IF(ISINSCOPE('Table'[Date]),SUM('Table'[item_qty]),_t_current-_t_pervious)

_new = 
var _currentmonth=SELECTEDVALUE('Slicer table'[Date])
var _previousmonth=CALCULATE(MAX('Slicer table'[Date]),DATEADD('Slicer table'[Date],-1,MONTH))
var _table_pervious=CALCULATETABLE(VALUES('Table'[id]),FILTER(ALL('Table'),'Table'[Date]=_previousmonth))
var _table_current=CALCULATETABLE(VALUES('Table'[id]),FILTER(ALL('Table'),'Table'[Date]=_currentmonth))
Return
IF(NOT(MAX('Table'[id]) in _table_pervious)&&MAX('Table'[id]) in _table_current,'Table'[variance],0)

_out = 
var _currentmonth=SELECTEDVALUE('Slicer table'[Date])
var _previousmonth=CALCULATE(MAX('Slicer table'[Date]),DATEADD('Slicer table'[Date],-1,MONTH))
var _table_previous=CALCULATETABLE(VALUES('Table'[id]),FILTER(ALL('Table'),'Table'[Date]=DATE(2021,5,1)))
var _table_current=CALCULATETABLE(VALUES('Table'[id]),FILTER(ALL('Table'),'Table'[Date]=DATE(2021,6,1)))
Return
IF(NOT(MAX('Table'[id]) in _table_current)&&MAX('Table'[id]) in _table_previous,'Table'[variance],0)

output = "New entities:"&SUMX(VALUES('Table'[id]),[_new])&"  "&"Outs:"&SUMX(VALUES('Table'[id]),ABS([_out]))

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!