Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

DAX Measure Monthly Difference

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 ...
  • bhanu_gautam's avatar
    bhanu_gautam
    2 years ago

    Anonymous , 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]
    )
bhanu_gautam's avatar
bhanu_gautam
Icon for Super User rankSuper User
2 years ago

Anonymous , 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]
)