Power BI: sum weekend values to show only week date

I have this incident table with some dates that fall in the weekend (Saturday and Sunday):

power bi

I want to calculate how many incidents I have but for my final results, I don’t want to show those weekends. I want to sum values of the weekend into the next week day, in this case, on Monday. If you are looking for the excel version, read Formula: sum weekend values to show only week date in an excel report.

First I will create a calendar with this formula by clicking on “home -> new table”:

power bi
var FullCalendar = ADDCOLUMNS(CALENDAR(MIN('table'[Date]),EOMONTH(MAX('table'[Date]),0)),"calendar",[Date],"dayname",FORMAT([Date],"DDD"))


power bi

I will create a relationship between both tables:

power bi

I will add a new column with this formula to calculate incident with priority P1:


power bi

Once done, I will repeat it for P2, P3, P4 and P5 with the same formula without forgetting to change “P1” by “P2”, “P3”, etc.

NOTE: change “table” and “argument” by yours

power bi

To show the results, in the “visualizations”, I will create a table and I will add those columns and in the “filters”, I will add “dayname” and I will select everything except “sat, sun or blank”:

Table Filters
power bi power bi

My final result:

Results without 0 Results with 0
power bi power bi

As we can see, the day 20 is not displaying, this is because for Friday, there are no incidents. To show 0 instead of “empty”, I will add an ISBLANK by using a variable to do it more “clear”:

var blank0=formula
power bi

To check if the calculation is correct, I can create another calendar without the name of the day:

power bi

I will create a relationship, it is the same type as the first one

power bi

And to calculate P1, this is the formula:


power bi

Alternatively, there is another solution to get the final result. If the first option is to use only 1 table, the second option is to use 2 tables, one with relationship (my calendar check) and one without so I will create a new table using the same formula as the first option:

power bi

As you may guess, this one has no relationship and for the “P1” formula:


power bi

The “visualizations” will be the same as the first option. I use this second option when I am not able to have 2 relationships between one table and my main one. This is all my tables:

power bi

Interesting Management