HOW TO DO BUSINESS DAYS CALCULATION

                                             

                                                  To calculate business days, we will need to consider all the holidays and weekends, in this
                                                  case we have counted only Sundays as weekend holidays and have added an existing
                                                   Holidays calendar table to our model.

                                                          ·       After acquiring the Holidays calendar, add it to the Power BI model and connect the 
                                       date column with the Date table.

 

                                                         ·        After connecting to the date table, we will create a new column on our main Date table

                                           and add the holidays to the new column by using a formula like this:

 

                              Holidays Text = RELATED(‘Z Holidays'[Holiday])

   

                                                          ·       Now that we have brought out the holidays into our date table, we will create a new
                                       column which will mark all holidays as 1 by using this formula:

                               Holidays = IF(‘Dates'[Holidays Text] = BLANK(), 0, 1)

 

                                                         ·      We will also create a column on our date table that marks all Sundays as 1 using this
                                      formula:

                                           Sundays = IF(‘Dates'[Day_Name] = “Sunday”, 1,0)

                                                         ·    Now we will create a new column which will mark all the working days as 1.

                              Working day = IF(‘Dates'[Sundays] = 1, 0, IF([Holidays] = 1, 0, 1))

 

                                                Now that we have created all the required columns, we will create a new measure that
                                                counts
 all the Business days using this formula:

 

                             Business Days Total = SUM(‘Dates'[Working day]) 


                                                                ·     Next, we will count all the business days that have passed till current day.

 

                            Business Days Passed =

 

                             CALCULATE(

 

                             SUMX(‘Dates’, ‘Dates'[Working day]),

 

                             FILTER(‘Dates’,
                            ‘Dates'[Date]<TODAY()))

 

·       
                                   Now that we have calculated business days and passed business days, we can get
the remaining business days.

                             Remaining business days = [Business Days Total] – [Business Days Passed]

 

                                                   ·    Now we have our remaining business days, I would recommend putting a month filter
                                 on the visual for current month only so that it would show the proper values.

  

HOW TO DO MTD CALCULATION

                                                         ·     First, we will sum the total sales using the SUM measure.

 

                            Retail Sales = SUM(SalesTable[Sales])



 

                                          ·    Now we can begin our MTD calculations, if you have done the business days calculations,
                                             we can get our MTD Dail Sales by dividing Total Sales by the number of business days that have
                                             passed.

                             MTD Daily Sales = DIVIDE([Retail Sales], [Business Days Passed])

                                           ·    If we are using a date filter on the visual or have put month as a row in a matrix, we
                                              can just use the Total Sales measure as our Sales MTD number.

 

                                                      ·    Now, let’s create a sum of our total goal so we can do MTD goal calculations.

 

 

                            Total Goal = SUM(Goals[Goal])

 

                                                      ·     Now we will create our daily sales goal by diving the total goal by total business days.

 


                             Daily Sales Goal = DIVIDE([Total Goal], [Business Days Total])

 

                                                     ·       Now that we have our daily sales goal, we can create our MTD Goal by multiplying the daily
                                    sales goal by number of business days that have passed.

                                       

 

                            Goal MTD = [Daily Sales Goal] * [Business Days Passed]

 

                                                    ·    Now that we have our goal we can easily compare our sales against MTD goal.

                            vs Goal MTD = [Retail Sales] – [Goal MTD]

 

                                                     ·    We can also create a percentage of the comparison

 

                            vs Goal MTD % = DIVIDE([vs Goal MTD], [Goal MTD])

 

                                                     ·    Now we will add projected sales: An example of projected sales is if a sales-person has had
                                  30 sales in 20 days, how many more sales can the salesperson achieve in the
                                  remaining days of the month?



                           Projected Sales = [MTD Daily Sales] * [Business Days Total]

 

                                                      ·       Next, we will add projected vs target/goal/budget.

                             Projected Sales vs Budget = [Projected Sales] – [Total Goal]

 

 

Scroll to Top