Calculate sales trends by trailing 4 quarters/4months

[Mohanad-Mustafa]

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

[Mohanad-Mustafa]

@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

[V-lianl-msft]

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.

[Mohanad-Mustafa]

Hi Liang, all good, the solution provided by Amit worked just fine, thanks for your help guys , you guys rock!

[amitchandak · Accepted 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.