cancel
Showing results for
Did you mean:  Helper III

## Monthly increased/decreased based on another column

Dear Experts,
I have a data table like below.

 Month Customer Name Total Fleet 1/31/2023 John 5 1/31/2023 Sue 7 1/31/2023 Dave 3 2/28/2023 John 4 2/28/2023 Sue 5 2/28/2023 Dave 7 2/28/2023 Moe 2

At the Jan 2023, we have rented total 15 fleets to individual customer.
At the Feb 2023, some customers returned some fleets and some rented more fleets.
I would like to create a chart based on month but not overall increased/decreased.
I would like to based on customer name as below.

 Month CustomreName Total Fleet Increased/Decreased 1/31/2023 John 5 1/31/2023 Sue 7 1/31/2023 Dave 3 2/28/2023 John 4 -1 2/28/2023 Sue 5 -2 2/28/2023 Dave 7 +4 2/28/2023 Moe 2 +2

if I select Feb 2023 in slicer, I would like to show Total increased = 6 and Total decreased = -3.
Thanks a lot for your help.

Regards,
KMT

2 REPLIES 2  Super User

@KyawMyoTun , If you need a column

new column =

var _date = [month]

var _value = sumx(filter(Table, [Customer Name] = earlier( [Customer Name])  && eomonth(_date , -1) = eomonth([Date],0) ), [Total])

return

[Total]  - _value

Or create a date table join with dates and use Time intelligence and create measures

example measures

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))

Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s  Helper III

Dear @amitchandak ,

When I try with calculate column, I didn't get the expected result as below. I have some measures with Time intelligence but those can only get total monthly increase not by customer name (e.g Total increase as 3) but I want to get total increase = 6 and total decrease = -3.
Here is the measure :

New Fleet =
var _currentMonth = MONTH(MAX(Sheet1[Month]))
var _currentYear = YEAR(MAX(Sheet1[Month]))
Var _previousMonth = IF(_currentMonth = 1, 12, _currentMonth -1)
Var _previousYear = IF(_currentMonth = 1, _currentYear -1, _currentYear)
Var _PreviousMonthCar =
CALCULATE(SUM(Sheet1[Total Fleet]),
MONTH(Calendar_Master[Date]) = _previousMonth,
YEAR(Calendar_Master[Date]) = IF(_currentMonth = 1, _previousYear, _currentYear)
)
Var CurrentMonthCar =
CALCULATE(
SUM(Sheet1[Total Fleet]),
MONTH(Sheet1[Month]) = _currentMonth,
YEAR(Sheet1[Month]) = _currentYear
)
Return
IF(CurrentMonthCar - _PreviousMonthCar > 0,CurrentMonthCar - _PreviousMonthCar,0)  