DAX Measure Monthly Difference

[DaniEMIT]

Hi All,

i've the following table named TURNOVER:

DATE	ID
01/05/2024	AAA
01/05/2024	BBB
01/05/2024	CCC
01/05/2024	DDD
01/06/2024	AAA
01/06/2024	BBB
01/06/2024	CCC
01/07/2024	AAA
01/07/2024	BBB
01/07/2024	DDD
01/07/2024	EEE
01/08/2024	BBB
01/08/2024	DDD
01/08/2024	FFF
01/08/2024	HHH
01/08/2024	LLL

Date is when the record is insert (1th of each month); ID is the key ID of the table.

I've to create a DAX that calculate for each month the positive (new record) and the negative difference (missing record) compared with the previous month.

For Example, on the 01/07/2024 i've two new record (IN) and one missing record (OUT) compared with june.

Date	RecordCount	IN	OUT
01/05/2024	4	-	-
01/06/2024	3	0	1
01/07/2024	4	2	1
01/08/2024	5	3	2

Which is in your opinion the best way?

I tried with the following DAX but it doesn't work properly:

_DipIN = 

VAR CurrentDate = CALCULATE(MAX(Turnover[Date]))

VAR PreviousDate=CALCULATE(MAX(Turnover[Date]),FILTER(ALL(Turnover),Turnover[Date]<CurrentDate))

VAR Dip=MIN(Turnover[ID])

VAR _Left=

SUMMARIZE(FILTER(Turnover,

         Turnover[Date]=PreviousDate),

         Turnover[ID],

         "QtyLeft",COUNT(Turnover[ID]))

VAR _Right= SUMMARIZE(FILTER(Turnover, 

                      Turnover[Date]=CurrentDate),

                      Turnover[ID],

                      "QtyRight",COUNT(Turnover[ID]))

return

COUNTROWS(EXCEPT(_Right,_Left))

[bhanu_gautam · Accepted solution]

@DaniEMIT , For this first create a measure for Previous month date

PreviousMonthDate =

   EDATE(Turnover[Date], -1)

Then create a calculated column for Record count 

RecordCount =

   CALCULATE(COUNT(Turnover[ID]), ALLEXCEPT(Turnover, Turnover[Date]))

Then Create a Measure for New Records (IN)

NewRecords =
VAR CurrentMonth = MAX(Turnover[Date])
VAR PreviousMonth = EDATE(CurrentMonth, -1)
RETURN
COUNTROWS(
EXCEPT(
VALUES(Turnover[ID]),
CALCULATETABLE(VALUES(Turnover[ID]), Turnover[Date] = PreviousMonth)
)
)

Create a Measure for Missing Records (OUT)

MissingRecords =

   VAR CurrentMonth = MAX(Turnover[Date])

   VAR PreviousMonth = EDATE(CurrentMonth, -1)

   RETURN

   COUNTROWS(

       EXCEPT(

           CALCULATETABLE(VALUES(Turnover[ID]), Turnover[Date] = PreviousMonth),

           VALUES(Turnover[ID])

       )

   )

Create a Summary Table:

SummaryTable =
SUMMARIZE(
Turnover,
Turnover[Date],
"RecordCount", [RecordCount],
"IN", [NewRecords],
"OUT", [MissingRecords]
)