Forum Discussion
Helper IVHow to generate daily level data from monthly level data? - Post No. 2
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
- 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.
Super UserHmm,
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_wHi 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 :smileyhappy:
- FirstDayInMonth