Calculate average weekly sales of current quarter to estimate quarter sales

[jhenscheid1]

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

[Jihwan_Kim · Accepted solution]

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]
)

[jhenscheid1 · Accepted solution]

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

[jhenscheid1]

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

[Jihwan_Kim · Accepted solution]

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]
)

[jhenscheid1]

@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

[Jihwan_Kim]

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.

[jhenscheid1]

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

[Jihwan_Kim]

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?