Help using matrix

[andfx]

Hi guys,

I have those databases for example:

categories.csv

id;name;category
1;fiat;ford
2;onix;gm
3;etios;toyota
4;macan;porsche
5;enzo;ferrari

history.csv

date;value;id
17/01/2019;1100;1
17/01/2019;1500;2
17/01/2019;700;3
17/01/2019;950;4
17/01/2019;230;5
18/01/2019;1100;1
18/01/2019;1500;2
18/01/2019;700;3
18/01/2019;950;4
18/01/2019;230;5
19/01/2019;1100;1
19/01/2019;1500;2
19/01/2019;700;3
19/01/2019;950;4
19/01/2019;230;5

predict_two.csv

date;value;id
20/01/2019;220;1
20/01/2019;500;2
20/01/2019;120;3
20/01/2019;230;4
20/01/2019;770;5

predict_one.csv

date;value;id
21/01/2019;830;1
21/01/2019;120;2
21/01/2019;250;3
21/01/2019;700;4
21/01/2019;430;5

I wish I could build this using matrix or maybe a table if it's possible:

I tried everything that I know, but I couldn't. I can't move data of predicts to history, because they need to be separated.

Thank you.

[MFelix]

Hi @andfx,

I assume that the I'd of categories tables connects with the history and predicts, you should create a calendar table and make relationships between calendar table and the history and calendar and predictions tables based on the date.

Then you just need to use the calendar table as you column headers ans place your historic and prediction on the values should give expected result.
Regards
MFelix

[andfx]

I tried it.. Not worked.

I made calendar

Calendar = 
CALENDAR ( MIN ( history[date] ); MAX ( predict_one[date] ) )

Did the relationships

The result:

Thank you.

[MFelix · Accepted solution]

Hi @andfx,

You need to have a 1 to Many relationship wiht a single cross filter direction, then add the following measure to your data:

Predict/history =
SWITCH (
    TRUE ();
    SUM ( History[value] ) <> 0; SUM ( History[value] );
    SUM ( Predict1[value] ) <> 0; SUM ( Predict1[value] );
    SUM ( Predict2[value] ) <> 0; SUM ( Predict2[value] );
    BLANK ()
)

Be aware that this formula is assuming that predit 1 and predict 2 includes diferrente dates and they are also not on the history table.

Result is below and attached PBIX file.

Regards,

MFelix

[andfx]

Thanks a lot, you're the best!

Be aware that this formula is assuming that predit 1 and predict 2 includes diferrente dates and they are also not on the history table.

Do you think it's possible to adapt that formula if dates on predit 1 and 2 they are on the history table too ?

Thank yo :))

[MFelix]

Hi @andfx,

How do you want ti handle that?

What values should be kept?

Regards,
MFelix