topic How to show the percentage change of sales from the previous month instead of the cumulative percent in DAX Commands and Tips

How to show the percentage change of sales from the previous month instead of the cumulative percent

Anonymous — Thu, 17 Nov 2022 12:25:51 GMT

Hi I am a Junior BI Developer.

This is training data i created to simulate what i am trying to do.

Which is to show just the Monthly percentage change in sales for the current month.

I have a solution but from my image below PowerBI keeps giving me the total percentage change highlighted in green.

I want the final value in the Alt MoM Sales % or the MoM Sales % Column which is 33.33% highlighted in red.

Formula Used is below:

Total Sales = SUMX('Fruit Sales', 'Fruit Sales'[Sales])

/* This is for previous months sales */

Last PM Sales = CALCULATE([Total Sales], PREVIOUSMONTH('Fruit Sales'[Date]))

MoM Sales = [Total Sales] - [Last PM Sales]

MoM Sales % = DIVIDE([MoM Sales], [Last PM Sales])

/*An alternative formula to find the previous month's sales */

Alt PM Sales = CALCULATE([Total Sales], PARALLELPERIOD('Fruit Sales'[Date], -1, MONTH))

Alt MoM Sales = [Total Sales] - [Alt PM Sales]

Alt MoM Sales % = DIVIDE([MoM Sales], [Alt PM Sales])

Re: How to show the percentage change of sales from the previous month instead of the cumulative percent

FreemanZ — Thu, 17 Nov 2022 13:18:02 GMT

try to add 'Fruit Sales'[Date] as the filter of 1.09% card visual. tick the date 15/12/2022.

Re: How to show the percentage change of sales from the previous month instead of the cumulative percent

Anonymous — Thu, 17 Nov 2022 14:41:33 GMT

It works for a selected date as you mention above i.e. 15/12/2022 but for between dates using a slicer which is what our team members will be using.

It still only gives the accumulative % change, not the monthly change.

Re: How to show the percentage change of sales from the previous month instead of the cumulative percent

v-yueyunzh-msft — Fri, 18 Nov 2022 02:05:26 GMT

Hi , Anonymous

According to your description, you want to show the "33%" in the "Total" of the colimn and in your card . Right?

Here are the steps you can refer to :
(1)This is my test data :

(2)We need to create three measres:

This Month = var _max_date = MAXX(ALLSELECTED('Table'),[Date]) return IF(HASONEVALUE('Table'[Date]), SUM('Table'[Sales]) ,CALCULATE(SUM('Table'[Sales]), 'Table'[Date]=_max_date))

Last Month = var _current_date= SELECTEDVALUE('Table'[Date]) var _t = FILTER(ALLSELECTED('Table') , MONTH('Table'[Date]) = MONTH( _current_date)-1) var _max_date = MAXX(ALLSELECTED('Table'),[Date]) var _t_last =SUMX( FILTER(ALLSELECTED('Table'), MONTH('Table'[Date]) = MONTH(_max_date)-1) , [Sales]) return IF(HASONEVALUE('Table'[Date]),SUMX(_t,[Sales]),_t_last)

MOM Sales % = DIVIDE([This Month]-[Last Month],[Last Month])

(3)Then we can put these measures on the visual and we will meet your need :

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!

Best Regards,

Aniya Zhang

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

Re: How to show the percentage change of sales from the previous month instead of the cumulative percent

Anonymous — Fri, 18 Nov 2022 10:04:28 GMT

Perfect!
Thank you so much.
I am curious how were you able to solve it?

Also, where did you learn to get so good at Dax calculations any courses, training, or practice routines?
I've been trying for a few hours and couldn't, thank you so much most Youtube videos get this wrong.