How to generate daily level data from monthly level data? - Post No. 2

[j_w]

There are two tables, a monthly Budget table (new  budget data will be added yearly, e.g. 2018-2019, 2019-2020 etc.):

Year    MonthNo    User        MonthlyBudget

2017   7                   David       5000.00

2017   8                   David       5000.00

... ...

2018   5                   David       5000.00

2018   6                   David       5000.00

2017   7                   James      3000.00

2017   8                   James      3000.00

... ...

2018   5                   James      3000.00

2018   6                   James      3000.00

And a Holiday table (new holiday data will be added yearly, e.g. 2019, 2020 etc.):

Date               Holiday

2017-01-02    New Year’s Day

2017-01-16    Birthday of Martin Luther King, Jr.

2017-02-20    Washington’s Birthday

2017-05-29    Memorial Day

2017-07-04    Independence Day

2017-09-04    Labor Day

2017-10-09    Columbus Day

2017-11-10    Veterans Day

2017-11-23    Thanksgiving Day

2017-12-25    Christmas Day

2018-01-01    New Year’s Day

2018-01-15    Birthday of Martin Luther King, Jr.

2018-02-19    Washington’s Birthday

2018-05-28    Memorial Day

2018-07-04    Independence Day

2018-09-03    Labor Day

2018-10-08    Columbus Day

2018-11-12    Veterans Day

2018-11-22    Thanksgiving Day

2018-12-25    Christmas Day

How to generate a Daily Budget table in Power BI as following ():

Date               User       DailyBudget*                               (NOTES for each row, is not a part of this table)

2017-07-01     David     0                                                   Saturday

2017-07-02     David     0                                                   Sunday

2017-07-03     David     5000/(Business days in July*)        Business day

2017-07-04     David     0                                                   Holiday

... ...

2018-06-29     David     5000/(Business days in June*)       Business day

2018-06-30     David     0                                                   Saturday

2017-07-01     James     0                                                   Saturday

... ... (the same rules for James as David's)

2018-06-29     James     3000/(Business days in June*)       Business day

2018-06-30     James     0                                                   Saturday

Note: 

*1. The value of field [DailyBudget] should be 0 when it is a holiday or falls in weekends.

*2. The Business days in month = Days in a month - Days of holiday or weekends

This post is an evolved version of the previous post:

http://community.powerbi.com/t5/Desktop/How-to-generate-daily-level-data-from-monthly-level-data/m-p...

Regards

[TomMartens · Accepted solution]

Hmm,

I'm not sure if I'm missing something, but hopefully this gets your started (here you can find a pbix file).

There are the following steps:

Step 1 - Data Preparation (The Query Section)

First I prepared a simple Calendar table in the query section.

This provides the possibility to Merge this table later on (to be precise a reference to this table called Budget Daily) with the Budget table. This Merge spreads the Budget table (User / Budget) across the appropriate days.

This table contains the columns "Date", "MonthIso", and "Year".

I created a colum "MonthIso" in the Budget table, this column is used for the Merge step.

I referenced the table Calendar and renamed it to "Budget Daily" and removed the columns "Year" and "MonthIso".

I merged the tables "Budget Daily"and "Budge". From the Budget table I'm just using the columns "User" and "Budget" from the table expansion step. I renamed the column "Budget" to "Budget Monthly".
I skipped filtering the Rows without a "Budget".



After doing this I close the query part and loaded the tables to the model

Step 2 - Data Modeling (Creating Table Relationships and Columns)

I created the following relationships

Holiday <-> Calendar (One to One / Both)

Budget Daily -> Calendar (Many to One / Single)

Then I created the following columns in the Calendar table (using DAX)

• WeekdayNo using the WEEKDAY() function to be able to differentiate between weekend and workdays, please be aware that subsequent steps are depending on the option you have chosen as 2nd parameter.
  I have chosen 2 (Weeks are starting on Monday), this means the IndexNo 6 and 7 are marking a weekend
• IsWorkday
  Checking if the day is either a Saturday or a Sunday (0) or not (1)
• IsHoliday (considering the Holiday table using RELATED())
  Checking if the Calendar day is in the Holiday, if this is the case, return 1 if not return 0
• IsBudgetDay
  Checking if the Calendar day is
  • A workday and not a holiday return 1
  • All other cases return 0
• BudgetDaysPerMonth
  Summing the column IsBudgetDay for each month returning the aggregated value to each day

Finally I created a column in the "Budget Daily" table: Budget Daily

Maybe this is a bit lenghty explanation, so please excuse if it does not help

[j_w]

Hi @TomMartens

Thank you so much. Your solution is very good 🙂

The only thing I found could be improved is that the start date and end date of the Calendar table is hard-coded:

Source = List.Dates(
Date.From("2017-01-01"),
Number.From(Date.From("2018-12-31")) - Number.From(Date.From("2017-01-01")) + 1,
#duration(1, 0, 0, 0)
)

The ideal start date and end date in Calendar table should be dynamically determined base on the first date of the earliest year-month and the last date of the latest year-month in Budget table. In this way, when the next year's budget data was added, the end date in Calendar table would be changed automatically.

But I am not familiar with the Power Query currently and not sure how to implement this 😞

If you could improve it, it would be a perfect example for learning.

Regards

[TomMartens]

Hey,

here is a modified solution (a new pbix file)

During the data preparation phase and before creating the Calendar table I created two new columns in the Budget table

• FirstDayInMonth
  This creates the first date for the month given by the columns Year and MonthNo
• LastDayInMonth
  Using the Function Date.EndOfMonth() with the new column "FirstDayInMonth"

To reference these colmns from the Calendar table I use the function Table.Column() in the Source step of the Calendar table

= List.Dates(
    List.Min(Table.Column(Budget, "FirstDayInMonth")),
    Number.From(List.Max(Table.Column(Budget, "LastDayInMonth"))) - Number.From(List.Min(Table.Column(Budget, "FirstDayInMonth"))) + 1,
    #duration(1, 0, 0, 0)
  )

Have a nicer weekend