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 Everyone
Can any one help me in achieving the MOM % trend in the below scenario.
I am using the trend of 13 months using the below formula.(Creating the what if parameter).
Formula:- Order 13 months =
CALCULATE (
SUM(Orders[Sales]),
DATESINPERIOD('Date'[Date], MAX ('Date'[Date]), -'N'[N Value],MONTH))
the above formula is working for sum of sales but when i use it for MOM it is showing only one month value or a straight line.
Order MOM 13 months =
CALCULATE (
[Sales MoM%],
DATESINPERIOD('Date'[Date], MAX ('Date'[Date]), -'N'[N Value],MONTH))
Attaching the PBIX file for reference.
Link to File: https://www.facebook.com/groups/powerbi/?multi_permalinks=2399590703388408¬if_id=1543064655543768...
Thanks in Advance.
Solved! Go to Solution.
Hi @Anonymous,
On the chart select the 3 dots and sort by month in the PBIX file it's sort by value.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsGot it:)
@MFelix, I have found a solution for this by creating a sort column in summary Table.
DAX formula:-
Hi @Anonymous,
First of all I can't get the file from the location you are pointing to, believe it's better to send a onedrive, google drive or dropbox, or something similar links than a facebook group.
Looking at your measure I assume you are using the SQL Jason Last N months sales post are you placing the correct columns on your visual to have the correct result?
In is post Jason creates two columns on the sales table (MonthYear and MonthYearNo) using the RELATED formula to the Calendar table and this are the ones that are used on the barchart not the month from the calendar table and the slicer is from the calendar.
Check if this is your issue and if you have any problems please share the file through one of the ways I refer above since I'm not abble to retrieve the file from facebook.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix,
Thanks for response.
Sharing via ondrive:- https://nablerin-my.sharepoint.com/:u:/g/personal/akhil_j_nabler_com/EWcyrBqwH3dNqhRROiXUe4kBxhV5xlR...
Iam using the SQL Jason Last N months sales post , placing the right columns in the bar chart.
Regards,
Akhil.
Hi @Anonymous,
I have found a solution for this problem, however yesterday I have tried to publish my solution and my post was deleted from the forum, don't understand why.
I will try to redo the post later.
Sorry for taking so long to answer.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix,
Thanks for the solution. Its perfectly working.
Could you please to sort the trend, trend is not getting sorted by Month and Year?
Regards.
Akhil.
Hi @Anonymous,
On the chart select the 3 dots and sort by month in the PBIX file it's sort by value.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix,
I have tried this by sorting Month and year, but the trend is not sorted.
Attaching the screenshot below. Do you have any other solution for this.
Got it:)
@MFelix, I have found a solution for this by creating a sort column in summary Table.
DAX formula:-
Hi @Anonymous,
Try the following.
Add a column to your date table:
EndOfMonht = EOMONTH('Date'[Date];0) // just used to make the summary table
Add a table with the following code:
Summary Table = FILTER(ADDCOLUMNS ( DISTINCT ( 'Date'[EndOfMonht] ); "CurrentYear"; [Sales Total]; "PreviousYear"; CALCULATE ([Sales Total]; DATEADD ( 'Date'[Date]; -1; YEAR ) ));[CurrentYear] <>BLANK())
Then add the following measures:
Sales Total = SUM(Orders[Sales]) MOM % 13 Months = CALCULATE ( (SUM('Summary Table'[CurrentYear]) - SUM('Summary Table'[PreviousYear])) / SUM('Summary Table'[PreviousYear]); DATESINPERIOD('Date'[Date]; MAX ('Date'[Date]); -'N'[N Value];MONTH)) Order 13 months measure = CALCULATE ( SUM('Summary Table'[CurrentYear]); DATESINPERIOD('Date'[Date]; MAX ('Date'[Date]); -'N'[N Value];MONTH))
Then just place them on your visual.
Not sure if this is what you need since it's adding a table, but to what I can check when adding the rolling average the context is lost for getting the previous year values. The new table only has 59 rows, so not a big number of information could work.
Check PBIX attach
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @Anonymous,
I have look at your issue and believe that the issue is regarding the fact that when you make the context be reduce to last N months you won't be abble to get the previous year value.
I have a solution that maybe cannot be that one you want because go to adding an additional table, but it works, and the additional table only as 59 rows so not much information because it's summarize by month.
Create a column on the Date table with the following code:
EndOfMonht = EOMONTH('Date'[Date];0)
Create the a summary table:
Summary Table = FILTER(ADDCOLUMNS ( DISTINCT ( 'Date'[EndOfMonht] ); "CurrentYear"; [Sales Total]; "PreviousYear"; CALCULATE ([Sales Total]; DATEADD ( 'Date'[Date]; -1; YEAR ) ));[CurrentYear] <>BLANK())
Relate it to the date table and then add the following measures:
Order 13 months measure = CALCULATE ( SUM('Summary Table'[CurrentYear]); DATESINPERIOD('Date'[Date]; MAX ('Date'[Date]); -'N'[N Value];MONTH)) MOM % 13 Months = CALCULATE ( (SUM('Summary Table'[CurrentYear]) - SUM('Summary Table'[PreviousYear])) / SUM('Summary Table'[PreviousYear]); DATESINPERIOD('Date'[Date]; MAX ('Date'[Date]); -'N'[N Value];MONTH))
Then just add the measures to your chart.
Check the PBIX file attach.
Again this solution may not be what you want since we are adding a new table but it works and it's dinamic so when you refresh the data it will also refresh.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering 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 |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |