Calculation of the 7-day average from a budget on month/year granularity

[Anonymous]

Dear PBI Community

My intention is the get a line chart where one can compare the average sales of the last 7 days with the budget.

The budget exists on a month/year granularity.

I was able to break it down on a daily level with the following formula:

Sales budget Daily =

DIVIDE (

CALCULATE (

CALCULATE (

[Umsatz netto BU],

TREATAS (

VALUES ( '1 Datum'[Jahr/Monatnr.] ),

SMB_FACT_BU[Jahr/Monatnr.]

)

),

ALL ( '1 Datum' ),

VALUES ( '1 Datum'[Jahr/Monatnr.] )

),

[#Working Days per Month]

) * [# Working Days]

However, when I wont to calculate the average of the last 7 days I don't get the right result. I'm sure it must have something to do with order where the calcuations are set, but I can't find the right solution. This is the formula I tried:

7 Days Ø Sales Budget per Workingday =

VAR _intervall = -7
VAR letzerFakDate = [MaxDate]
VAR UmsatzNetto =
    CALCULATE (
        [Sales budget Daily],
        DATESINPERIOD ( '1 Datum'[Datum], letzerFakDate, _intervall, DAY )
    )
VAR Divisor =
    CALCULATE (
        [# Working Days],
        DATESINPERIOD ( '1 Datum'[Datum], letzerFakDate, _intervall, DAY )
    )
VAR result =
    DIVIDE ( UmsatzNetto, Divisor )
RETURN
    result

Thank you for your help

[v-angzheng-msft · Accepted solution]

Hi, @Anonymous 

The company disabled it, so let's see what else we can do

You already had the measure(Sales budget daily) ,right?

I was able to break it down on a daily level with the following formula:

Sales budget Daily =

DIVIDE (

CALCULATE (

CALCULATE (

[Umsatz netto BU],

TREATAS (

VALUES ( '1 Datum'[Jahr/Monatnr.] ),

SMB_FACT_BU[Jahr/Monatnr.]

)

),

ALL ( '1 Datum' ),

VALUES ( '1 Datum'[Jahr/Monatnr.] )

),

[#Working Days per Month]

) * [# Working Days]

Have you tried my above measure?  and what result you get, the result is not what you want?

please use this measure in you pbix.

_7Days =
VAR _t =
    SELECTCOLUMNS (
        ADDCOLUMNS ( ALL ( 'Table' ), "_Sales budget Daily", [Sales budget Daily] ),
        "_Date", [Date],
        "_Working Days", [# Working Days],
        "_Sales budget Daily", [Sales budget Daily]
    )
VAR _currentDate =
    MAX ( 'Table'[Date] )
VAR _table7days =
    FILTER ( _t, [_Date] <= _currentDate && [_Date] > _currentDate - 7 )
VAR _countDays =
    SUMX ( _table7days, [_Working Days] )
VAR _sumSales =
    SUMX ( _table7days, [Sales budget Daily] )
VAR _result =
    DIVIDE ( _sumSales, _countDays )
RETURN
    _result

Please test if the measure works. Any update I will reply as soon as possible during working hours tomorrow.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Now, I got it. Your above measures works!

Thank you!

[v-angzheng-msft]

Hi, @Anonymous 

Try to create a measure like this:

_7Days = 
var _t=SELECTCOLUMNS(ADDCOLUMNS(ALL('Table'),"_Sales budget Daily",[_Sales budget Daily]),"_Date",[Date],"_Working Days",[# Working Days],"_Sales budget Daily",[Sales budget Daily])

var _currentDate=MAX('Table'[Date])

var _table7days=FILTER(_t,[_Date]<=_currentDate&&[_Date]>_currentDate-7)

var _countDays=SUMX(_table7days,[_Working Days])

var _sumSales=SUMX(_table7days,[_Sales budget Daily])

var _result=DIVIDE(_sumSales,_countDays)
return _result

Result:

Since your sales budget daily is coming from the measure, then I also use a measure to get the daily sales budget column instead.
It must be noted that the data in the first second row is incorrect (blank) due to the context.

Please refer to the attachment below for details. Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Dear Zeon Zheng

Unfortunately, I don't have the budgt on day granularity. As a result, the Sales budget daily isn't a column but a measure itselfs.

I tried to upload a sample PBI, but I don't know how.

Thank you and best regards,

[v-angzheng-msft]

Hi, @Anonymous 

Yes, I treat it as a measure to work with. In my PBIX file you can see that I created a measure with a SUM function. I got the above measure by treating the Sales budget daily as a measure.

Does the above measure works? With what I've done, I think it will work.
If it doesn't work, please upload the sample Pbix file. I will reply as soon as possible during tomorrow's office hours.

How to provide sample data in the Power BI Forum

1) Uploading files

Especially when you have a problem with a DAX statement or the data model, it would be best to provide a pbix-file containing the sample data. You might have seen posts here in the forum where files are directly attached and have searched for the button to do exatly that. But unless you're a Microsoft employee or a super user this feature will not be available for you. Instead you have to upload your file to a cloud storage (like OneDrive, Dropbox, Google Drive or Wetransfer for example) and paste the link to that storage location in the post.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Dear Zeon Zheng

Thanks for the hint regard the upload. Here is my link for the PBI sample file:

PBI sample 

The measure has to be previously calculated to get a daily basis. So, I can't simply sum it.

I hope the pbi attachement works.

Best regards

[v-angzheng-msft]

Hi, @Anonymous 

Unable to access the file, you need to set the file access permission to public, you can open the link in the privacy window to test if it works.

[Anonymous]

Sorry. My company has disabled it.

I post a picture of the model. It shows the budget on a monthly/year basis. Which must first be broken down to a daily basis before the 7-day average can be calculated.

Thank you.

[v-angzheng-msft]

Hi, @Anonymous 

Try this:

7 Days Ø Sales Budget per Workingday =
VAR _intervall = -7
VAR letzerFakDate = [MaxDate]
VAR UmsatzNetto =
    CALCULATE (
        [Sales budget Daily],
        '1 Datum'[Datum]
            > MAX ( '1 Datum'[Datum] ) - _intervall
            && '1 Datum'[Datum] <= MAX ( '1 Datum'[Datum] )
    )
VAR Divisor =
    CALCULATE (
        [# Working Days],
        '1 Datum'[Datum]
            > MAX ( '1 Datum'[Datum] ) - _intervall
            && '1 Datum'[Datum] <= MAX ( '1 Datum'[Datum] )
    )
VAR result =
    DIVIDE ( UmsatzNetto, Divisor )
RETURN
    result

Or, please share some dummy data and draw a simple picture to show your expected visual so that I may work out with a more accurate measure.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Dear Zeon Zheng

Thank you. I tried your formula, but got an error:

"A MAX type function was used in a Trure/False expression that serves as a table filter expression. This is not allowed."

At date 01.02.2021 I expect 4'305 but with my calculation I get 3898.

If it helps, I can prepare a PBI-File.

Best regards,

[v-angzheng-msft]

Hi, @Anonymous 

Based on the picture above, is the expected column the sales budget for the last 7 days divided by the number of working days?

Pbix files are better if it's convenient for you.