Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Appreciate any help for this.
Thank you.
Solved! Go to Solution.
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.
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.
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |