# Weekly Average Excluding Dates with No (Zero) Orders

I have a twofold problem:

1. I would like to add to my Date table, a column that shows WorkDay = Yes or No.
No = any date from the Order table where the \$ amount (of orders) is less than or equal to \$0

2. Using this, I would like to calculate an average weekly sales from the Sales Table based only on Yes Workdays.

For example, in week 1, if we have 4 workdays and \$1,000 total sales, my average = \$250.
In week 2, if we have 5 workdays and \$1,000 total sales, my average is \$200.

Sample file attached.

Sample Dashboard v09-13-2021.pbix (268.7 KB)

Thank you for any suggested solutions.

ScoTPA

Hi @ScottTPA ,

``````WorkDay =

// sum amounts for all dates from date table
var sumfordate = CALCULATE(SUM('Orders-sample'[Amount]),ALLEXCEPT(DateTable,DateTable[Date]))

return
// some dates will be blank (no orders) or smaller than 0, those will be flagged as "no", others "yes"
IF(ISBLANK(sumfordate) || sumfordate < 0,"no","yes")
``````

I hope this works for you

Sabine

Hello @ScottTPA,

Thank You for posting your query onto the Forum.

Iâ€™m providing couple of methods to achieve the solution -

1. Showcasing the results at a Weekly Level.

To achieve this type of results simply write the measures as provided below -

``````Total Sales =
SUM( 'Sales-sample'[Amount] )

No Of Workdays =
VAR _No_Of_Workdays =
COUNTROWS(
FILTER( DateTable ,
DateTable[IsWorkingDay] ) )

RETURN
IF( ISBLANK( [Total Sales] ) ,
BLANK() ,
_No_Of_Workdays )

No Of Workdays - Net =
SUMX(
SUMMARIZE(
DateTable ,
DateTable[Date] ,
"@Total" ,
[No Of Workdays] ) ,
[@Total]
)

Average Sales =
DIVIDE( [Weekly Sales.] , [No Of Workdays - Net] , 0 )
``````

The reason why I wrote the â€śNo Of Workdays - Netâ€ť measure is just to fix the Grand Total of â€śNo Of Workdaysâ€ť. If you want the results at a row level and want to avoid the results at a Grand Total level then you can simply avoid writing that measure.

So once this measures are written just drag the â€śWeek & Yearâ€ť column from the Date table and the measures which we created into the matrix. These type of method simply consolidate the numbers at a weekly level and then divide it with the number of working days to achieve the â€śAverage Resultsâ€ť. Below is the screenshot of the results provided for the reference -

1. Showcasing the results at a Daily Level.

But now letâ€™s say you want to analyze the results at a daily level i.e. something like this. Below is the screenshot provided for the reference -

So in this case, numbers are at a granular level and needs to be consolidated at a weekly level.

In that case, thereâ€™re two types of methods available one is â€śMeasure Branchingâ€ť technique and other one is â€śVariable Techniqueâ€ť. Letâ€™s see both of those techniques and the measures that are provided below for the reference -

1. Measure Branching Technique

``````Weekly Sales. =
IF( ISBLANK( [Total Sales] ) ,
BLANK() ,
CALCULATE( [Total Sales] ,
ALLEXCEPT( DateTable , DateTable[Week & Year] ) ) )

No Of Workdays - Net. =
IF( ISBLANK( [Total Sales] ) ,
BLANK() ,
CALCULATE( [No Of Workdays - Net] ,
ALLEXCEPT( DateTable , DateTable[Week & Year] ) ) )

Average Sales =
DIVIDE( [Weekly Sales.] , [No Of Workdays - Net.] , 0 )
``````

2. Variables Technique -

Using this technique, you can avoid writing so many measures i.e. right from â€śNo Of Workdays - Netâ€ť measure till the last above provided measure. Everything will be covered under the one roof/measure. Below is the consolidated measure provided for the reference -

``````Average Sales Using Variables =
VAR _Calculation_Of_No_Of_Workdays_Net =
CALCULATE(
VAR _No_Of_Workdays_Net =
SUMX(
SUMMARIZE(
DateTable ,
DateTable[Date] ,
"@Total" ,
[No Of Workdays] ) ,
[@Total] )

RETURN
_No_Of_Workdays_Net ,
ALLEXCEPT( DateTable , DateTable[Week & Year] ) )

VAR _Weekly_Sales =
CALCULATE( [Total Sales] ,
ALLEXCEPT( DateTable , DateTable[Week & Year] ) )

RETURN
IF( ISBLANK( [Total Sales] ) ,
BLANK() ,
DIVIDE( _Weekly_Sales , _Calculation_Of_No_Of_Workdays_Net , 0 ) )
``````

Now, once this measure is written, just drag the â€śDateâ€ť, â€śWeek & Yearâ€ť fields from the Date table and the measure which weâ€™ve created and if you donâ€™t want to showcase the â€śWeek & Yearâ€ť field in your table visual then hide that field.

So you can adopt either of the approach to showcase the results as per the scenario.

Iâ€™m also attaching the working of the PBIX file for the reference alongwith some of the reference links of the posts/videos that might be useful to you.

Hoping you find this useful and meets your requirements that youâ€™ve been looking for.

Important Note: I replaced the old M Code of Date table with the new M Code of Date table.

1 Like

Thank you @Sabine . I tried this and for some reason, I donâ€™t get any results for â€śNoâ€ť. Though my data has some dates when I donâ€™t have sales. Also, I realized I have to use Sales <= 0, not Orders <=0 so ignore the Orders table.

Hi @Harsh , thank you for the reply. I am working through your solution. How can I add holidays to the Date Table from your new M Code?

Hi @ScottTPA,

As 4th parameter this date table function takes Holidays as list
So just add a query containing all these dates in a date datatype (so not a datetime datatype) that are considered to be a holiday. Once youâ€™ve called that in the 4th argument, the IsHoliday column will return a boolean value that you can add as a filter condition.

Hi @ScottTPA ,

please excuse me for not being more precise.
I ment to add the calculated column to the date table.

Also, your orders table (and I guess your sales table as well) does not contain all possible dates. Thatâ€™s why you will only get â€śyesâ€ť as a result, because you only have data for dates with orders, but there wonâ€™t be a row with the corresponding date if no orders were made.

I hope this addition to my previous post helps.

Hello @ScottTPA,

To create a â€śHoliday Tableâ€ť in Power BI. Our experts @Melissa and @BrianJ had created several videos on this topic and on our forum challenges were also hosted by our experts and members provided wide variety of solutions in those challenges. Iâ€™m providing a link below of the posts from our Forum, blog links as well as video links for the reference purposes.

Hoping youâ€™ll find this useful and meets your requirements that youâ€™ve been looking for.

Note: In order to view the entire Forum posts, please click onto the link and not onto the â€śexpand/collapseâ€ť button.

Thanks and Warm Regards,
Harsh

https://forum.enterprisedna.co/t/perpetual-holiday-calendar-problem-of-the-week-1-dax-solution/13594

https://forum.enterprisedna.co/t/how-to-create-a-holiday-table-in-power-bi-time-intelligence-in-power-bie/8061

@Sabine , thank you for the clarification. I will make the change and try your solution.
@Melissa, thank you. I added the query and I see how it now has holidays. I will also watch the tutorials.

@Harsh, I canâ€™t thank you enough for the elegant and detailed solution. This is really very good and it works well. I need to study it more so I can understand better how and why it works and I need to watch the videos you provided. I am very impressed with your ability to understand and explain this. Thank you.

I use Enterprise DNA periodically as I only occasionally work on PowerBI projects. The quality of this forum and the solutions provided gets better every time I use it. Itâ€™s really impressive.

Hello @ScottTPA,

Youâ€™re Welcome.

We all are glad that we were able to assist and serve you. And weâ€™re happy that your experience on the Forum is just getting better every time.

All the very best with your Power BI projects and in case you hit any difficulty you know where to find us.

Have a great and happy weekend ahead.

Thanks and Warm Regards,
Harsh