Measure Design Help - multi-slice time based calculations for sales projections

[Anonymous]

Hello again all,

I am progressing with this project but hit another wall with the visual. The objective is to have tables and graphs where per region and per sales stage, you can view the gap between pipeline target amount (calculated from other tables) vs the actual pipeline amount. two views might be:

Region	this month Stage 1 gap	stage 2 gap	stage 3 gap	.....
AFR
EUR
APA
...

Region	Sales stage	Current month+3	Current month+4	current month +5
APA	Closed Won	stage 6 gap
APA	negotiating		stage 5 gap
APA	proposing			stage 3/4 gap
....WCA...	....

Each pipeline stage has a predefined velocity to close. so i created measures month by month to get the gap that must be made up, then created measures for each stage target and actual.

Each region has its own forecast and pipeline. so i connected both through the region table and both are linked to a date table to

sync months

I have dynamically created the target for each stage in its own measure for the [current] month (not sure if there is any easier way to do this?).

The problem is i dont see how to better structure the measures so that a table can generate a per-sales-stage target on one measure. or simply, relate the "StageCurrentMonthAct" or "StageCurrentMonthTarget" to the actual stage name? the measure only outputs a number, not a relation to the stage.

I may be over-confusing the problem here so looking for help to simplify this

[Stachu · Accepted solution]

did you manage to solve it yourself? If not I have a suggestion for simplification

Looking at measure [2MonthRofoGap] - variable monthtarget could be dynamic (coming from filter context, e.g. slicer/table). You could use the same logic with [StageXCurrMonthTarget]. In this scenario you would do Matrix visual with single measure and Stages/Months in columns