Relationship problem

[paris]

Hi all,

I have forecast and actual data in diffenet format and want to combine in report format. 

I can not change forecast from monthly basis to daily basis. Could you please help me to set up relationship?

All the best,

LoveParis

[dax]

Hi paris,

You need three tables, Actual, Foreaast and calendar, then you could create a column in actual table like below

Column = SWITCH(Actual[city],"Tokyo","Japan", "Japan","Japan", "London","UK", "Paris", "France")

create the relationship like below

Then create measures like below

actual =
CALCULATE (
    SUM ( Actual[productions] ),
    FILTER ( ALL ( Actual ), Actual[date] = MIN ( 'calendar'[Date] ) )
)

forcast =
CALCULATE (
    SUM ( Forcast[production] ),
    FILTER (
        ALLEXCEPT ( Forcast, Forcast[Country] ),
        MONTH ( Forcast[date] ) = MONTH ( MIN ( 'calendar'[Date] ) )
    )
)

Then you could create table like below

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[paris]

Hi Zoe,

Thank you very much for your reply!

1) I do not need to build relationship between calender and forecast/actual?

2) People keep adding new cities. Instead of using switch function, can I creat a new table country/city and build relationship with forecast/actual? 

All the best,

LoveParis

[dax]

Hi paris, 

Yes, you don't need to create relationship between calendar and actual,. And if you want to use another country table, you could create relationship like below

You could refer to my pbix file for more details.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[paris · Accepted solution]

Hi Zoe,

Thank you for sending me the demo file.

1) Is there any reason actual is under calender?

2) I use slicer to select by country or city. 

3) However..   I set up relationships (same as yours) but it seems not working. I got total value (sum of paris, london, tokyo) for each day. Is there any possible reason causing this problem? 

All the best,

LoveParis

[paris]

Zoe,

Thank you very much for your help. I was able to solve my problem (it was caused because I did not set up many to many relation ship for both fact tables)

Thank you again!

All the best

LoveParis