Mini challenge - how to create common month dimension for 2 measures

[letileti]

Hi all,

someone who can help me achieving this result?

many Thanks for looking into it

Result:

Month;amount (launch);amount(target)

Jan;120;50

feb;o;200

i would like to have the breakdown at lower levels (project) too, but ignored it to simplify

Original data:

Project;month(launch);amount(launch);month(target);amount(target)

1;jan;100;feb;200

2;jan;20;jan;50

[sunny_talwar · Accepted solution]

What you need here is to use a Canonical Date here

[sunny_talwar · Accepted solution]

Simplified version of the script for the simplified data you provided

Table:
LOAD Project,
	 Month(Date#(Launch_Month, 'MMM')) as Launch_Month,
	 Launch_Amount,
	 Month(Date#(Target_Month, 'MMM')) as Target_Month,
	 Target_Amount;
LOAD * INLINE [
    Project, Launch_Month, Launch_Amount, Target_Month, Target_Amount
    1, Jan, 100, Feb, 200
    2, Jan, 20, Jan, 50
];

DateBridge:
LOAD Project,
	 Launch_Month as Month,
	 'Launch' as DateType
Resident Table;
LOAD Project,
	 Target_Month as Month,
	 'Target' as DateType
Resident Table;

[letileti]

Thank you so Much! I read the post you shared and that is exactly what I need! Going to try it out immediately