topic Re: Calculate average weekly sales of current quarter to estimate quarter sales in DAX Commands and Tips

Calculate average weekly sales of current quarter to estimate quarter sales

jhenscheid1 — Mon, 01 Aug 2022 21:20:55 GMT

Hello, I am trying to calculate average weekly sales of the current fiscal quarter so that I can use those results to estimate what the quarterly results will be. I think I am making this more difficult that what it needs to be, but am coming up at a loss to figure this out.

In excel, I could go to the formula and simply divide out by the number of the current fiscal week to get the average, but I want to be able to do that automatically within Power BI using a dax formula.

Below is a screen shot of the current numbers. And so for quarter 3, I want to take the total ($1,804,215 so far) and divide that by the number of fiscal weeks (5) to get my average. Easy enough to do with a formula, but when fiscal week 32 rolls around, I want to have the same formula calulate the average. I would then want that average to be in its own column so that it can represent the pace for that quarter. When the 4th quarter starts, I would need the process to start over.

Thanks in advance

Re: Calculate average weekly sales of current quarter to estimate quarter sales

Jihwan_Kim — Tue, 02 Aug 2022 03:14:14 GMT

Hi,

I am not sure how your caledar table looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file. I hope the below can provide some ideas on how to create a solution for your dataset.

Avg quarterly: = AVERAGEX ( ADDCOLUMNS ( CALCULATETABLE ( SUMMARIZE ( 'Calendar', 'Calendar'[Fiscal Quarter], 'Calendar'[Fiscal Week] ), ALLEXCEPT ( 'Calendar', 'Calendar'[Fiscal Quarter] ) ), "@sales", [Sales measure:] ), [@sales] )

Re: Calculate average weekly sales of current quarter to estimate quarter sales

jhenscheid1 — Tue, 02 Aug 2022 13:48:09 GMT

Jihwan_Kim Thank you for the help! This solution worked. Much appreciated.

Re: Calculate average weekly sales of current quarter to estimate quarter sales

jhenscheid1 — Wed, 10 Aug 2022 19:18:59 GMT

Jihwan_Kim Hello. I present the solution you provided and the results, but was informed I needed to carry forward the average value through the following quarter. So in my original request where I stated "I would then want that average to be in its own column so that it can represent the pace for that quarter. When the 4th quarter starts, I would need the process to start over..." I was wrong in that the value would need to start over. I tried to work through the calculations you provided, but am not getting it to work. Could you take a look for me and provide a new solution?

Thanks

Re: Calculate average weekly sales of current quarter to estimate quarter sales

Jihwan_Kim — Thu, 11 Aug 2022 03:38:07 GMT

Hi,

Sorry that I do not understand. Could you please provide a sample pbix file's link with Q4 data included and also with how the expected numbers/visualization look like? And then I can try to look into it to come up with a more accurate solution.

Re: Calculate average weekly sales of current quarter to estimate quarter sales

jhenscheid1 — Thu, 11 Aug 2022 13:23:16 GMT

Hello and sorry for the confusion.

So the previous solution you provided allowed me to create a measure called Weekly Avg where I am able to estimate future weeks based on actuals, but it currently only calculates for the current Fiscal Quarter. I need to carry those values forward to the next Fiscal Quarter (see screen shot). From that I can easily calculate a value for the Quarterly Pace column.

Weekly Avg Formula:

Weekly Avg =

AVERAGEX (

ADDCOLUMNS (

CALCULATETABLE (

SUMMARIZE ( 'Datefile_Yesterday', Datefile_Yesterday[Fiscal Quarter], Datefile_Yesterday[Fiscal Week] ),

ALLEXCEPT ( 'Datefile_Yesterday', Datefile_Yesterday[Fiscal Quarter] )

"@sales", [Netsales]

[@sales]

)

The other column, Cumulative Net Sales, which may be impacting the results is calculated by:

Cumulative Net Sales =

CALCULATE([Netsales],

FILTER(ALL('Datefile_Yesterday'),

Datefile_Yesterday[Transaction Date] <= MAX(Datefile_Yesterday[Transaction Date])

&& Datefile_Yesterday[Fiscal Year] = MAX(Datefile_Yesterday[Fiscal Year])

)

Please let me know if more information is needed.

Thanks

Re: Calculate average weekly sales of current quarter to estimate quarter sales

Jihwan_Kim — Fri, 12 Aug 2022 06:31:47 GMT

Hi,

Thank you for your explanation.

However, in your screen capture, Quart-4 NetSales is blank. May I ask how do you want to see this as a weekly average?

Re: Calculate average weekly sales of current quarter to estimate quarter sales

jhenscheid1 — Fri, 12 Aug 2022 13:31:57 GMT

Hello, Jihwan_Kim . See below screen shot. I was able to reach out to one of our developers who solved this so I will mark this as solved. For your reference, below are the DAX measurements they created for this.

Weekly Avg

Quarter Pace

Thanks