Creating a cumulative line from a single number/budget

[questionpbi]

Bascially, I just need a line that accumulates evenly over time(periods), and eventually end up on a total. Seems easy, but there are a few caveats.

Context:

• The data shown below are an overly simplified oversion of what I'm working with. The actual data contain millions of entries and hundreds of columns.
• "Period" here is essentially months. But because they're not calculated the exactly the same as calendar months, date related formulas would not apply.

Table 1: 

• The empty rows in Table 1 are simulating how the data is captured in the database. Supposedly, we are at the end of period 5, and therefore, we only have the Period and Spending data generated up to period 5.

Team	Period	Spending
1	1	$ 35,969.00
1	2	$ 55,478.00
1	3	$ 13,549.00
1	4	$ 57,476.00
1	5	$ 42,247.00
1
1
1
1
1
2	1	$ 71,358.00
2	2	$ 35,969.00
2	3	$ 55,478.00
2	4	$ 35,969.00
2	5	$ 55,478.00
2
2
2
2
2
3	1	$ 57,476.00
3	2	$ 42,247.00
3	3	$ 55,478.00
3	4	$ 13,549.00
3	5	$ 57,476.00
3
3
3
3
3

Table 2:

• Table 2 only contain the final budget amound for each employee. It is not linked to periods in the database.

Team	Employee	Budget
1	A	$       93,839.00
1	B	$     154,593.00
1	C	$     193,405.00
2	A	$     158,394.00
2	B	$       83,849.00
2	C	$     138,405.00
3	A	$     158,394.00
3	B	$     145,937.00
3	C	$     138,405.00

Requirement:

• I need a budget line that incrementally increases each period all the way to the last period where it meets the budget amount.
• Have the expenditure and cumulative expenditure data shown on the same visual, and have the "Team" filter be applied to all compenents in the visual
  • The cumulative expenditure is already setup, the only issue here is getting the budget line to connect to the periods properly.

Additional thoughts:

• My initial approach is to do "DIVIDE(SUM(Table2[Budget]), 10)" to get the amount per period, then accumulate this amount over each period. But I can't find a way to make it show properly in the visual, because these tables are not linked by periods in anyway
• Another approach is to generate a "period" column or index for each team's budget totals. But I also failed to find an efficient way to do this.

This my first post and I've already searched for hours for a solution to this, but could not find one. I'm hoping I can get some answers from the community very soon. Thank you very much!

[parry2k]

@questionpbi this was clearly pointed out here, and then later I asked. Good luck!

super easy would be to add another table called Period with distinct values and then connect it with both the tables and also use it on the x-axis, problem solved.

[parry2k]

@questionpbi I think if you follow what I explained and try we will not be going back and forth. If you are asking for help I would recommend first testing the solution provided to you.

[questionpbi]

Your previous reply was "did you create the period table with up to 10  periods and then use that on the x-axis and have a relationship with the Table 1." 

And I just answered with what I did...

But I think you meant to suggest that as a solution, not a question. I think you could have typed just a few more words to clearly communicate what you meant.

Either way, I'ved tried that, but ran into an unrelated issue in the actual data I'm working with.

Thank you

[parry2k]

@questionpbi did you create the period table with up to 10  periods and then use that on the x-axis and have a relationship with the Table 1.

[questionpbi]

No, the relationship is simply this:

The reason why it shows 10 periods in the visual is because I manually changed the range of X axis to 10. I don't usually have to do this because in the real thing, I have other data and measures in there that goes up period 10.

[parry2k]

@questionpbi try this measure for Budget and use that for Line

Budget Measure = 
DIVIDE ( SUM ( Table2[Budget] ), 10 ) * SELECTEDVALUE ( Table1[Period Column] ) 

[questionpbi]

This worked up to period 5, but doesn't run all the way to period 10, because the future periods aren't yet in the system. It would have worked if I had the future periods already in there.

Do you know a good way to generate the future periods for each year?

[parry2k]

@questionpbi super easy would be to add another table called Period with distinct values and then connect it with both the tables and also use it on the x-axis, problem solved.

[questionpbi]

There's no period to connect with in Table 2. If you mean something else, could you please show an example?