Can I append a subset of table rows back to the table itself, multiple times?

[zerosugar]

Hi all,

I'm having some trouble with something that I'd like to do. Essentially I have a dataset that consists of multiple Revenue Forecasts, along with Actual values for those forecasts. The Actual values are updated as time goes on. Same goes for the Forecasts. The data looks something like this:

Jump Off Date	Revenue Date	Forecast Name	Business Division	Revenue
	3/31/2019	Actuals	Management Consulting	206
	6/30/2019	Actuals	Management Consulting	144
	9/30/2019	Actuals	Management Consulting	275
	12/31/2019	Actuals	Management Consulting	103
	3/31/2020	Actuals	Management Consulting	288
	6/30/2020	Actuals	Management Consulting	151
	3/31/2019	Actuals	Tech Consulting	97
	6/30/2019	Actuals	Tech Consulting	81
	9/30/2019	Actuals	Tech Consulting	61
	12/31/2019	Actuals	Tech Consulting	70
	3/31/2020	Actuals	Tech Consulting	74
	6/30/2020	Actuals	Tech Consulting	65
9/30/2019	9/30/2019	Q3 2019 Optimistic Forecast	Management Consulting	275
9/30/2019	12/31/2019	Q3 2019 Optimistic Forecast	Management Consulting	103
9/30/2019	3/31/2020	Q3 2019 Optimistic Forecast	Management Consulting	288
9/30/2019	6/30/2020	Q3 2019 Optimistic Forecast	Management Consulting	151
9/30/2019	9/30/2020	Q3 2019 Optimistic Forecast	Management Consulting	251
9/30/2019	12/31/2020	Q3 2019 Optimistic Forecast	Management Consulting	294
9/30/2019	9/30/2019	Q3 2019 Optimistic Forecast	Tech Consulting	97
9/30/2019	12/31/2019	Q3 2019 Optimistic Forecast	Tech Consulting	81
9/30/2019	3/31/2020	Q3 2019 Optimistic Forecast	Tech Consulting	61
9/30/2019	6/30/2020	Q3 2019 Optimistic Forecast	Tech Consulting	70
9/30/2019	9/30/2020	Q3 2019 Optimistic Forecast	Tech Consulting	148
9/30/2019	12/31/2020	Q3 2019 Optimistic Forecast	Tech Consulting	138
9/30/2019	9/30/2019	Q3 2019 Stress Forecast	Management Consulting	275
9/30/2019	12/31/2019	Q3 2019 Stress Forecast	Management Consulting	103
9/30/2019	3/31/2020	Q3 2019 Stress Forecast	Management Consulting	288
9/30/2019	6/30/2020	Q3 2019 Stress Forecast	Management Consulting	151
9/30/2019	9/30/2020	Q3 2019 Stress Forecast	Management Consulting	158
9/30/2019	12/31/2020	Q3 2019 Stress Forecast	Management Consulting	150
9/30/2019	9/30/2019	Q3 2019 Stress Forecast	Tech Consulting	97
9/30/2019	12/31/2019	Q3 2019 Stress Forecast	Tech Consulting	81
9/30/2019	3/31/2020	Q3 2019 Stress Forecast	Tech Consulting	61
9/30/2019	6/30/2020	Q3 2019 Stress Forecast	Tech Consulting	70
9/30/2019	9/30/2020	Q3 2019 Stress Forecast	Tech Consulting	33
9/30/2019	12/31/2020	Q3 2019 Stress Forecast	Tech Consulting	47

Here's an example of a visual I want to see:

What I want is 2 things: 

1. Where "Actuals" values overlap with the Q3 2019 Optimistic and Stress Forecast, the "Actual" values should not appear. I just want to show the Forecast values. It's also important to note that the Forecasts (I'll always only want to look at two at once) may not have the same jump-off/start quarters. One forecast could start Q3 2019 and another could start Q4 2019. 

2. Where the value is just "Actuals" I'd like to somehow "duplicate" that and have one of them become "Q3 2019 Optimistic Forecast" and the other become "Q3 2019 Stress Forecast". Basically I need to do this so I can show to full year 2019 combined actual/forecast of both Q3 2019 Optimistic Forecast and Q3 2019 Stress Forecast. These forecasts names will change in the future. 

So essentially I need to append the "Actuals" time series data to the beginning of each Forecast for each Business Division. And I need to make sure the name of that appended data in the [Jump Off Name] column is the same as the Forecast name to which it was appended. 

How would I do something like this? I've been spinning my wheels on this trying to get something going, but I'm not making any progress. All I can think to do is use the CROSSJOIN() function, but I'm not sure how to build on that. Does anyone have any tips?

Any help would be greatly appreciated. Thank you!

[lbendlin]

You would use a disconnected dates table with the quarters,  cross join that with the Consulting types, and then create two measures  (optimistic/stress) that would calculate according to your rules.

Is this sufficient or do you need to see a sample implementation?

[zerosugar]

Hi, thank you for the reply! I appreciate the help very much. Is it possible you can provide a sample implementation? I'm having some trouble with this. I've done this so far:

The tables: 

All tables

Date Table of unique revenue dates:

 All unique revenue dates (will this be problematic with multiple unique jump-off dates?)

Cross-joining the dates table values with the forecast names:

Crossjoining the revenue dates with the jump off names

The base data table:

Total Data

A waterfall chart visual I'd like to show Q1 and Q2 actuals as a part of. 

Waterfall chart visual - I'd like to grab the Q1 and Q2 2019 values from actuals and add them to both the optimistic and the stress forecast. Q3 from Actuals is equal to both forecast Q3's so we don't need it.

For the waterfall chart, I want Q1 and Q2 actuals to be appended to both the Optimistic and Stress forecast. Ideally, I'd like for this not to be hardcoded, because the jump-off quarter will change frequently. I just wantthere to be some sort of formula that fills missing quarterly values for the year with values from the "Actuals" jump off name. That way all values are represented and we get an apples-to-apples comparison. 

In the future, I may compare a Q2 2019 Optimistic Forecast to a Q4 2019 Optimistc Forecast. So, if I add Q1 actuals to the first one, and Q1, Q2, and Q3 actuals to the second one, it's more of an apples-to-apples forecast. It might also be okay to just compare values for dates that overlap between the selected forecast dates, but I think showing full year with the help of actuals is a better view.

Is this possible? I'm not sure how to do this in DAX. I'd appreciate any insight you can provide. Thank you!

[lbendlin]

Please provide your sample data in usable format.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

[zerosugar]

Here's the dataset that I'm using. I've added an Optimistic and Stress Forecast for Q2 2019 Jump Off as well, so that I can ensure functionality when comparing different jump-off dates. 

Jump Off Date	Revenue Date	Forecast Name	Business Division	Revenue
	3/31/2019	Actuals	Management Consulting	118
	6/30/2019	Actuals	Management Consulting	174
	9/30/2019	Actuals	Management Consulting	210
	12/31/2019	Actuals	Management Consulting	225
	3/31/2020	Actuals	Management Consulting	299
	6/30/2020	Actuals	Management Consulting	123
	3/31/2019	Actuals	Tech Consulting	83
	6/30/2019	Actuals	Tech Consulting	72
	9/30/2019	Actuals	Tech Consulting	82
	12/31/2019	Actuals	Tech Consulting	68
	3/31/2020	Actuals	Tech Consulting	99
	6/30/2020	Actuals	Tech Consulting	54
9/30/2019	9/30/2019	Q3 2019 Optimistic Forecast	Management Consulting	210
9/30/2019	12/31/2019	Q3 2019 Optimistic Forecast	Management Consulting	293
9/30/2019	3/31/2020	Q3 2019 Optimistic Forecast	Management Consulting	67
9/30/2019	6/30/2020	Q3 2019 Optimistic Forecast	Management Consulting	273
9/30/2019	9/30/2020	Q3 2019 Optimistic Forecast	Management Consulting	327
9/30/2019	12/31/2020	Q3 2019 Optimistic Forecast	Management Consulting	479
9/30/2019	9/30/2019	Q3 2019 Optimistic Forecast	Tech Consulting	83
9/30/2019	12/31/2019	Q3 2019 Optimistic Forecast	Tech Consulting	106
9/30/2019	3/31/2020	Q3 2019 Optimistic Forecast	Tech Consulting	131
9/30/2019	6/30/2020	Q3 2019 Optimistic Forecast	Tech Consulting	172
9/30/2019	9/30/2020	Q3 2019 Optimistic Forecast	Tech Consulting	132
9/30/2019	12/31/2020	Q3 2019 Optimistic Forecast	Tech Consulting	165
9/30/2019	9/30/2019	Q3 2019 Stress Forecast	Management Consulting	210
9/30/2019	12/31/2019	Q3 2019 Stress Forecast	Management Consulting	70
9/30/2019	3/31/2020	Q3 2019 Stress Forecast	Management Consulting	2
9/30/2019	6/30/2020	Q3 2019 Stress Forecast	Management Consulting	215
9/30/2019	9/30/2020	Q3 2019 Stress Forecast	Management Consulting	21
9/30/2019	12/31/2020	Q3 2019 Stress Forecast	Management Consulting	202
9/30/2019	9/30/2019	Q3 2019 Stress Forecast	Tech Consulting	83
9/30/2019	12/31/2019	Q3 2019 Stress Forecast	Tech Consulting	32
9/30/2019	3/31/2020	Q3 2019 Stress Forecast	Tech Consulting	18
9/30/2019	6/30/2020	Q3 2019 Stress Forecast	Tech Consulting	16
9/30/2019	9/30/2020	Q3 2019 Stress Forecast	Tech Consulting	2
9/30/2019	12/31/2020	Q3 2019 Stress Forecast	Tech Consulting	96
6/30/2019	6/30/2019	Q2 2019 Optimistic Forecast	Management Consulting	174
6/30/2019	9/30/2019	Q2 2019 Optimistic Forecast	Management Consulting	234
6/30/2019	12/31/2019	Q2 2019 Optimistic Forecast	Management Consulting	53
6/30/2019	3/31/2020	Q2 2019 Optimistic Forecast	Management Consulting	218
6/30/2019	6/30/2020	Q2 2019 Optimistic Forecast	Management Consulting	262
6/30/2019	9/30/2020	Q2 2019 Optimistic Forecast	Management Consulting	384
6/30/2019	6/30/2019	Q2 2019 Optimistic Forecast	Tech Consulting	174
6/30/2019	9/30/2019	Q2 2019 Optimistic Forecast	Tech Consulting	84
6/30/2019	12/31/2019	Q2 2019 Optimistic Forecast	Tech Consulting	105
6/30/2019	3/31/2020	Q2 2019 Optimistic Forecast	Tech Consulting	138
6/30/2019	6/30/2020	Q2 2019 Optimistic Forecast	Tech Consulting	106
6/30/2019	9/30/2020	Q2 2019 Optimistic Forecast	Tech Consulting	132
6/30/2019	6/30/2019	Q2 2019 Stress Forecast	Management Consulting	174
6/30/2019	9/30/2019	Q2 2019 Stress Forecast	Management Consulting	56
6/30/2019	12/31/2019	Q2 2019 Stress Forecast	Management Consulting	2
6/30/2019	3/31/2020	Q2 2019 Stress Forecast	Management Consulting	172
6/30/2019	6/30/2020	Q2 2019 Stress Forecast	Management Consulting	16
6/30/2019	9/30/2020	Q2 2019 Stress Forecast	Management Consulting	162
6/30/2019	6/30/2019	Q2 2019 Stress Forecast	Tech Consulting	174
6/30/2019	9/30/2019	Q2 2019 Stress Forecast	Tech Consulting	26
6/30/2019	12/31/2019	Q2 2019 Stress Forecast	Tech Consulting	15
6/30/2019	3/31/2020	Q2 2019 Stress Forecast	Tech Consulting	13
6/30/2019	6/30/2020	Q2 2019 Stress Forecast	Tech Consulting	2
6/30/2019	9/30/2020	Q2 2019 Stress Forecast	Tech Consulting	76

This is the output graph I'm getting currently using fields from "Total_Data":

I have 2019 selected as the year in this exampe. 

My issue here is that the values for the Forecasts are not Full Year values, even though they could be if the Actuals are included. So, Q2 2019 Optimistic Forecast is showing Q2, Q3, and Q4 values, with Q2 being an actual value and Q3 and Q4 being forecasted (the first date in the forecast - "predicted quarter 0" - is an actual value). Q3 2019 is the same but with Q3 and Q4.

The issue is when I'm comparing this two forecasts, it's not really apples-to-apples, because they don't compare the same quarters. I'd like to tack the actuals (which we have for 2019) onto both of these forecasts and see what the comparison is.

So for Q2 2019 Optimistc Forecast, that means the value should be 924, instead of 824.

And for Q3 2019 Optimistic Forecast we should have 1137 instead of 602.

Those would be the values for 2019. 

It would be even better if I could tack the actuals onto partial year forecasts when Power BI knows that actuals exist for the entire year (meaning Q1, Q2, Q3, and Q4). But if Power BI recognizes that we don't have actuals for the full year such that we can get the two forecasts to have matching revenue overlap dates (for example, if we did't have any 2019 actuals, meaning Q2 2019 would exist in the Q2 Optimistic Forecast but not in the Q3 Optimistic Forecast), then it only keeps the dates that overlap. That would ensure the comparisons are always apples-to-apples, so to speak. Not sure if that would be possible though.

[lbendlin]

I think there are two avenues you could pursue.  One would be to use COALESCE to haul in actuals where they exist, and the forecast where there are no actuals. The other avenue could be to choose a different visual like (for example)  a line chart

I don't fully understand your business rules - maybe someone more versed in forecasting can chime in.