Average Sales last 3 months and Sparkline

[joshua1990]

I would like to add a column into a matrix visual that shows me the average monthly sales for the last 3 months based on a month selection. The month selection is based on a slicer.

The challenge is our calendar is a fiscal calendar. In the same calendar I have these columns:

• Year
• Week
• Month

How can I define a measure that will show the AVG weekly sales for the previous 3 months.

But also I want to have a sparkline that will have the months on the x-axes.

How would you define the dax measure?

This is not working with a fiscal calendar:

AverageSalesLast3Months = 
CALCULATE(
    AVERAGE(Sales[SalesAmount]),
    DATESINPERIOD(Calendar[Date], MAX(Calendar[Date]), -3, MONTH)
)

[maruthisp · Accepted solution]

Hi joshua1990 ,

As per my understand and with sample data , I tried to implement the solution for you.
Please find the attached pbix file:
Average Sales last 3 months and Sparkline.pbix

Please let me know if you have further questions.

If this reply helped solve your problem, please consider clicking "Accept as Solution" so others can benefit too. And if you found it useful, a quick "Kudos" is always appreciated, thanks! 

Best Regards, 

Maruthi 

LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/ 

X            -  Maruthi Siva Prasad - (@MaruthiSP) / X

[v-venuppu]

Hi @joshua1990 ,

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please accept it as a solution and give it a 'Kudos' so other community members with similar problems can find a solution faster.

Thank you.

[v-venuppu]

Hi @joshua1990 ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-venuppu]

Hi @joshua1990 ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-venuppu · Accepted solution]

Hi @joshua1990 ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @maruthisp for the Prompt Response.

Here’s a solution that respects a fiscal calendar. First, create a FiscalMonthIndex column in your Calendar table:

FiscalMonthIndex = Calendar[FiscalYear] * 100 + Calendar[FiscalMonthNumber]

Then, use this measure:

AverageSalesLast3FiscalMonths =
VAR SelectedMonthIndex = MAX ( Calendar[FiscalMonthIndex] )
VAR Last3Months =
FILTER (
ALL ( Calendar ),
Calendar[FiscalMonthIndex] <= SelectedMonthIndex
&& Calendar[FiscalMonthIndex] > SelectedMonthIndex - 3
)
RETURN
CALCULATE (
AVERAGEX (
VALUES ( Calendar[FiscalMonthIndex] ),
CALCULATE ( SUM ( Sales[SalesAmount] ) )
),
Last3Months
)

This will correctly average sales for the last 3 fiscal months based on the slicer selection. For the sparkline, use FiscalMonth on the x-axis and ensure it's sorted by FiscalMonthIndex.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thank you.

[maruthisp · Accepted solution]

Hi joshua1990 ,

As per my understand and with sample data , I tried to implement the solution for you.
Please find the attached pbix file:
Average Sales last 3 months and Sparkline.pbix

Please let me know if you have further questions.

If this reply helped solve your problem, please consider clicking "Accept as Solution" so others can benefit too. And if you found it useful, a quick "Kudos" is always appreciated, thanks! 

Best Regards, 

Maruthi 

LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/ 

X            -  Maruthi Siva Prasad - (@MaruthiSP) / X