Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Solved! Go to Solution.
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
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
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.
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.
try to add 'Fruit Sales'[Date] as the filter of 1.09% card visual. tick the date 15/12/2022.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
21 | |
20 | |
15 | |
13 |
User | Count |
---|---|
45 | |
41 | |
39 | |
19 | |
19 |