Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
KyawMyoTun
Helper III
Helper III

Monthly increased/decreased based on another column

Dear Experts,
I have a data table like below.

MonthCustomer NameTotal Fleet
1/31/2023John5
1/31/2023Sue7
1/31/2023Dave3
2/28/2023John4
2/28/2023Sue5
2/28/2023Dave7
2/28/2023Moe2

 

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.

MonthCustomre
Name
Total FleetIncreased/Decreased
1/31/2023John5 
1/31/2023Sue7 
1/31/2023Dave3 
2/28/2023John4-1
2/28/2023Sue5-2
2/28/2023Dave7+4
2/28/2023Moe2+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
amitchandak
Super User
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

Dear @amitchandak ,

  Thanks a lot for your reply.
When I try with calculate column, I didn't get the expected result as below.

KyawMyoTun_0-1684914865708.png

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)
Can you please help me on this again?

Regards,
KMT

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors