Hi, I greatly appreciate any help you can offer.
I have a twofold problem:
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
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.
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’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 -
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 -
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 -
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.
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.
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.
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.
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.
@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.
