Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello Team Awesome,
Are there more than one method to calculate sales trends with trailing 4 months and 4 quarters and 4 years?
i.e: does it have to be through a power BI measure? if yes, kindly provide me some guide.
Or can we just use a visualization chart such as bar chart or a column chart to help us achieve this target?
By Sales Rep | 2019 Fiscal YTD | 2020 Fiscal YTD | 2021 Fiscal YTD | 2022 Fiscal YTD |
By Customer | Quarter -3 | Quarter - 2 | Quarter -1 | Current Quarter |
By Category | Month -3 | Month -2 | Month -1 | Month |
I have a sales table with total sales column and a separate calendar table that have fiscal month, quarter and year from
2019 and up to recent day.
Any help is greatly appreciated.
Thanks
Mohanad
Solved! Go to Solution.
@Mohanad-Mustafa , Not very clear. Check if these can help
trailing QTR = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,QUARTER))
trailing 2 QTR = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-2,QUARTER))
trailing 3 QTR = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-3,QUARTER))
trailing 4 QTR = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-4,QUARTER))
Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Month))
2 Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-2,Month))
3 Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-3,Month))
4 Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-4,Month))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
2 Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-2,Year))
3 Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-3,Year))
4 Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-4,Year))
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
@amitchandak , thanks a lot as usual, love your quick replies. I just wanted to share this table with you below as it wasn't clear in my previous message, it seems that I need to show current quarter and month sales but show previous quarters and months for previous years by trail
Hi @Mohanad-Mustafa ,
Try to put the measures provided earlier and the date in the calendar table into the visual.
If misunderstand what you mean, please tell us what you expect in the bar chart.
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Liang, all good, the solution provided by Amit worked just fine, thanks for your help guys , you guys rock!
@Mohanad-Mustafa , Not very clear. Check if these can help
trailing QTR = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,QUARTER))
trailing 2 QTR = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-2,QUARTER))
trailing 3 QTR = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-3,QUARTER))
trailing 4 QTR = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-4,QUARTER))
Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Month))
2 Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-2,Month))
3 Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-3,Month))
4 Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-4,Month))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
2 Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-2,Year))
3 Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-3,Year))
4 Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-4,Year))
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
User | Count |
---|---|
96 | |
87 | |
78 | |
72 | |
69 |
User | Count |
---|---|
113 | |
105 | |
84 | |
65 | |
64 |