Search code examples
daxpowerpivot

Find the last balance quantity per customer per month (Power Pivot)

I have the below dataset (Table 1), and I would like to create a new calculated column to only have the last Balance Qty per month for each customer.

The last column (EOM Balance Qty) illustrates what I need.

  -----------------------------------------------------
  |                     Table 1                       |
  ----------------------------------------------------- -------------------
  |     Date    | Customer | Type | Qty | Balance Qty | | EOM Balance Qty |
  |=============|==========|======|=====|=============| |=================|
  |  1/Jan/2019 | Alan     | Buy  |  15 |          15 | |                 |
  |-------------|----------|------|-----|-------------| |-----------------|
  |  2/Jan/2019 | Alan     | Use  |  10 |           5 | |               5 |
  |-------------|----------|------|-----|-------------| |-----------------|
  |  2/Feb/2019 | Alan     | Buy  |  30 |          35 | |                 |
  |-------------|----------|------|-----|-------------| |-----------------|
  |  3/Feb/2019 | Alan     | Use  |  10 |          25 | |              25 |
  |-------------|----------|------|-----|-------------| |-----------------|
  |  3/Mar/2019 | Alan     | Use  |  15 |          10 | |              10 |
  |-------------|----------|------|-----|-------------| |-----------------|
  |  4/Apr/2019 | Alan     | Buy  | 100 |         110 | |                 |
  |-------------|----------|------|-----|-------------| |-----------------|
  |  5/Apr/2019 | Alan     | Use  |  35 |          75 | |              75 |
  |-------------|----------|------|-----|-------------| |-----------------|
  |  1/Jan/2019 | Bob      | Buy  |  10 |          10 | |                 |
  |-------------|----------|------|-----|-------------| |-----------------|
  |  2/Jan/2019 | Bob      | Use  |  10 |           0 | |               0 |
  |-------------|----------|------|-----|-------------| |-----------------|
  | 20/Jan/2019 | Charlie  | Buy  | 100 |         100 | |             100 |
  |-------------|----------|------|-----|-------------| |-----------------|
  |  1/Feb/2019 | Charlie  | Use  |  20 |          80 | |              80 |
  |-------------|----------|------|-----|-------------| |-----------------|
  |  5/Apr/2019 | Charlie  | Use  |  10 |          70 | |                 |
  |-------------|----------|------|-----|-------------| |-----------------|
  | 30/Apr/2019 | Charlie  | Use  |  55 |          15 | |              15 |
  ----------------------------------------------------- -------------------

Sample Excel File Here

I have tried searching for a similar solution and the closes I got is this. However, I am getting an error when I add it as a calculated column or measure in Power Pivot.

I have also tried using the ENDOFMONTH function, but some dates didn't return the result as they should.

= CALCULATE ( SUM ( Table1[closing_balance_qty] ), ENDOFMONTH ( Table1[Date] ) )

Any help is much appreciated.

Solution

  • Updated based on the comments:

    I modified your new source data as follows:

    enter image description here

    Instead of the previous column "Date", we will now use column "Date-Time", which contains date + time.

    You will also need to add field "Year-Month" at the source file, not as a calculated column. Either ask your data source provider to add it for you, or use PowerQuery.

    Next, create a calculated column:

    EOM Balance Qty = 
VAR Last_Date_Time =
    CALCULATE ( 
      MAX ( Table1[Date-Time] ), 
      ALLEXCEPT ( Table1, Table1[Customer], Table1[Year-Month] ) 
    )
RETURN
    IF ( Table1[Date-Time] = Last_Date_Time, CALCULATE(SUM ( Table1[Balance Qty] )))

    Result:

    enter image description here

    How it works:

    • First, we group data by Customer and Year-Month using ALLEXCEPT, and find max date-time within the group.
    • Then, for the current record, we compare its date-time with the latest date-time we have found. If they are the same, we calculate the balance, otherwise return blank.