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:

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

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.

DateRecordCountINOUT
01/05/20244--
01/06/2024301
01/07/2024421
01/08/2024532

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'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]
    )

1 Reply

  • bhanu_gautam's avatar
    bhanu_gautam
    Super 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]
    )