Filter the last balance quantity per customer per month

[Raymond-O]

Hi All,

I have a dataset (the blue columns), 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 in Green illustrate what I need.

Sample Data File Here 

Appreciate any help for this.

Thank you.

[v-yuta-msft · Accepted solution]

@Raymond-O ,

Click query editor-> Add columns-> Index column, then create a calculate column using dax below:

EOM Balance Qty = 
VAR Last_Qty_Index = CALCULATE(MAX('Table'[Index]), FILTER('Table', MONTH('Table'[Date]) = MONTH(EARLIER('Table'[Date])) && 'Table'[Customer] = EARLIER('Table'[Customer])))
VAR Qty = CALCULATE(MAX('Table'[Balance Qty]), FILTER('Table', 'Table'[Index] = Last_Qty_Index))
RETURN
IF('Table'[Index] = Last_Qty_Index, Qty, BLANK())

  

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.