Use last date if empty

[joshua1990]

Hi all!

I have a budget table that stores our budgets for each quarter. Now, this table is refreshed once every quarter with an effective data when the new period has started:

Effective Date	Budget	Department
01.01.2023	500000	A
05.03.2023	600000	B

I would like to use this information in a bar chart that has the week number on the x axes.

But since the table above has just 1 date for each quarter I need to expand the date somehow.

How would you do this?

[Martin_D · Accepted solution]

Hi @joshua1990 ,

The process I'd recommend includes the following steps:

1. Define the allocation rules, i.e., how should the quarterly budget be allocated to days or weeks: Same amount per day/week, some saisonality from past periods applied as proportional factors, etc.? If you do not allocate amounts to days but to weeks instead, what are the rules for handling partial weeks at the beginning and end of the quarter? Are e.g. workday/weekend based rules relevant here? Another challenge with your data could be that quarters do not always seem to start at the first day of the quarter (e.g. at 05.03.2023). Without knowing the start date of the next quarter, allocation will get fuzzy. Is there a rule when each quarter will start, like at the first Sunday of each calendar quarter, or can you provide the end date of the quarter or the start date of the next quarter before loading the data? This is purly conceptional work and can be done on a piece of paper, a whiteboard, or any documentation tool of your choice. Any vagueness or uncertainty or implicit assumptions in this specification will typically just fuel discussions about the values in your organization.
2. Implement the allocation rules in Power Query and load daily or weekly budget amounts: Running the allocation at load time will result in simpler and faster DAX measures.
3. Specify the exact naming rules for your weeks. Be aware that in some week numbering schemes like ISO 8601 the same number can occur for two different weeks in the same calendar year, like week no. 1 at the beginning and end of the same calendar year, and specify how they should occur and be distinguished on the axis of your bar chart if needed (if you only want to show one quarter at once there is no conflict).
4. Create a date table that contains the week number and a sorting column for the week if needed and a year column if needed according to your specification and create a relationship between the date table and the allocated budget table.
5. Create a measure to show the budget in your bar chart. Probably something simple like SUM('Budget Table'[Budget Amount]) will do the job.
6. Create the bar chart and add the date column from the date table and the budget measure. A bar chart will need the date on the y-axis, for using the date on the x-axis you need a column chart.

BR

Martin