Calculate Monthly Percentage Change with a Filtered List

[PaulHet]

I have a dataset of many different Projects and the financial data associated with each of them spanning multiple months. This is currently all in one table, so each row in the table is essentially File Month, Project, Profit (in that month). What I want to do is calculate the % difference of the profit field each month by project. All the articles I've read are for doing this wholesale, I can't find any help on how to do this for a filtered list. 

So the output would be that I would see that for Project A, it had a 10% increase in profitiability in Feb compared to Jan, followed by a 6% increase in March compared to Feb etc. And I would see that for Project B, it had a -5% decrease in profitability in Feb compared to Jan and so on.

I've tried doing this with the CALCULATE DAX function, but it's just bringing out null values if I pass previous month and Project as filters. 

[v-yetao1-msft · Accepted solution]

Hi @PaulHet 

Please correct me if I wrong understood your question .

You want to calculate the benefit percentage for the specified month and the previous month separately by item category, right ? I created a simple sample, you can refer to it .

Original data :

Create a measure to count the percentage of diff between each month .

Percentage =
var _previous=CALCULATE(SELECTEDVALUE('Table'[Profit]),PREVIOUSMONTH('Table'[Date]),ALLEXCEPT('Table','Table'[Project]))
var _diff=(SELECTEDVALUE('Table'[Profit])-_previous)/SELECTEDVALUE('Table'[Profit])
return IF(ISBLANK(_previous),"",_diff)

_previous is to return the profit from last month, _diff is to return the percentage of diff between two month .

The final result is as shown :

I have attached my pbix file , you can refer to it .

Best Regard

Community Support Team _ Ailsa Tao

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

[PaulHet]

This is perfect, thank you so much!

[amitchandak]

@PaulHet , If you have a month and year, create a date. Then you can use time intelligence. Or have a separate month year table with rank on Year Month (YYYYMM)

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]))

new column

Month Rank = RANKX(all('Date'),'Date'[Month Start date],,ASC,Dense)

This Month = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])))
Last Month = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])-1))

Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA

[PaulHet]

 Hi @amitchandak , 

Thank you for your response! It does not quite solve my problem, however it does help me get a little closer (I have now created a data table and related it to the FileDate in my original data). I downloaded your MTD PBIX file to use as a reference to better explain my problem below.

What I want to do is essentially get the MTD differences for different Item name or City name as you have in your data. So where your DAX is just summing all the sales data and then comparing it month by month, I'd want to compare January's Brand 8 data compared to February's Brand 8 data and so on. 

My data is relatively simple as I only have a single timepoint for each month, so it's already 'summed' if that makes sense, so it's just comparing single points of data across months filtered by 'Item Name'