How do I calculate the average daily order trend for the previous 3 months, 6 months, 12 months?

[Libbyb23]

I am needing to calculate the average value of sales invoiced and ordered in the same month for a rolling 3, 6, and 12 months. I have created a few measure:

Daily trend is used to calculate what the monthly trend should be based on the number of working days for the month.
I created a daily trend measure below (the last part updates for 6 and 12 months):

Daily Order Trend = DIVIDE(CALCULATE(SUM('Sales Data'[sales_price]), FILTER('Sales Data', 'Sales Data'[order_date].[MonthNo] = 'Sales Data'[invoice_date].[MonthNo])), [Rolling 3m Working Days])

I created a measure for rolling 3, 6, and 12 months that look like:

Rolling 3m Working Days =

CALCULATE([Working Days Total], DATESINPERIOD('Date Dimension'[FullDateAlternateKey], [Today], -3, MONTH

))

However, when I updated to the 6 and 12 month measures, it was just dividing the daily order trend value buy those numbers of days and not calculating the average value of orders placed and invoiced during the 3, 6, or 12 month times.

[rajendraongole1 · Accepted solution]

Hi @Libbyb23 - I hope you already have a seperate date table which is essential to work on time intelligence functions , if not please create one date table using new table and create a relationship with your sales table and Date table.

Calculating the average daily orders for the previous 3, 6, and 12 months:

Hope you already calculated 

Sales_Invoiced_Ordered_Same_Month =
CALCULATE(
SUM('Sales Data'[sales_price]),
FILTER(
'Sales Data',
MONTH('Sales Data'[order_date]) = MONTH('Sales Data'[invoice_date]) &&
YEAR('Sales Data'[order_date]) = YEAR('Sales Data'[invoice_date])
)
)

To calcualte rolling averages, 

Rolling3Months_Sales =
CALCULATE(
[Sales_Invoiced_Ordered_Same_Month],
DATESINPERIOD(
'DateTable'[Date],
MAX('DateTable'[Date]),
-3,
MONTH
)
)

Rolling6Months_Sales =
CALCULATE(
[Sales_Invoiced_Ordered_Same_Month],
DATESINPERIOD(
'DateTable'[Date],
MAX('DateTable'[Date]),
-6,
MONTH
)
)

Rolling12Months_Sales =
CALCULATE(
[Sales_Invoiced_Ordered_Same_Month],
DATESINPERIOD(
'DateTable'[Date],
MAX('DateTable'[Date]),
-12,
MONTH
)
)

similarly you calculate for Rolling3months_Days,Rolling6months_Days,Rolling12months_Days

calculate Average Daily Order Value by dividing the DIVIDE([Rolling3Months_Sales], [Rolling3Months_Days]),6 months, 12 months.

The above measures will calculate the average daily orders for the last 3, 6, and 12 months

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[rajendraongole1 · Accepted solution]

Hi @Libbyb23 - I hope you already have a seperate date table which is essential to work on time intelligence functions , if not please create one date table using new table and create a relationship with your sales table and Date table.

Calculating the average daily orders for the previous 3, 6, and 12 months:

Hope you already calculated 

Sales_Invoiced_Ordered_Same_Month =
CALCULATE(
SUM('Sales Data'[sales_price]),
FILTER(
'Sales Data',
MONTH('Sales Data'[order_date]) = MONTH('Sales Data'[invoice_date]) &&
YEAR('Sales Data'[order_date]) = YEAR('Sales Data'[invoice_date])
)
)

To calcualte rolling averages, 

Rolling3Months_Sales =
CALCULATE(
[Sales_Invoiced_Ordered_Same_Month],
DATESINPERIOD(
'DateTable'[Date],
MAX('DateTable'[Date]),
-3,
MONTH
)
)

Rolling6Months_Sales =
CALCULATE(
[Sales_Invoiced_Ordered_Same_Month],
DATESINPERIOD(
'DateTable'[Date],
MAX('DateTable'[Date]),
-6,
MONTH
)
)

Rolling12Months_Sales =
CALCULATE(
[Sales_Invoiced_Ordered_Same_Month],
DATESINPERIOD(
'DateTable'[Date],
MAX('DateTable'[Date]),
-12,
MONTH
)
)

similarly you calculate for Rolling3months_Days,Rolling6months_Days,Rolling12months_Days

calculate Average Daily Order Value by dividing the DIVIDE([Rolling3Months_Sales], [Rolling3Months_Days]),6 months, 12 months.

The above measures will calculate the average daily orders for the last 3, 6, and 12 months

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[Libbyb23]

Hi!

For this, is there a way to use previous 3 months based on the date (today)? For example, having the view be the previous 3 months from June 3. So march 3, April, 3, and May 3?

[rajendraongole1 · Accepted solution]

Hi @Libbyb23 - you can try below logic to get the same.

3 Month Trend =
CALCULATE(
SUM('Sales Data'[sales_price]),
FILTER(
'Sales Data',
'Sales Data'[Order Date Trend] = 'Sales Data'[Invoice Date Trend]
),
DATESINPERIOD(
'Date Dimension'[FullDateAlternateKey],
EOMONTH(TODAY(), -1), -- This gets the end of the last month
-3,
MONTH
)
)

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[Libbyb23]

Hi!

Thanks again!! Yes that worked perfect.