Referencing two tables

Resolved · High Priority · Version 2016

Thu 27 Feb 2020, 14:55 Delegate Luca said...

Luca has attended:
Power BI Reporting course

Referencing two tables

Hi

I have an actuals and a forecast column within a table that I need to merge.

I want to write an if formula that references a "Report Date" column in my Date Table.

I essentially want to create a new column with the following if statement:

IF(Date<='DateTable'[Repot Date], Actual, Forecast)

However, when attempting to reference the column in my date table, I am unable to do so.

Thu 27 Feb 2020, 16:15 Edited on Thu 27 Feb 2020, 16:16 Trainer Jens said...

RE: Referencing two tables

Hi Luca,

Thank you for the forum question.

Is the date column in the table related to the 'DateTable'[Repot Date]?

Do you want to test if the date is less than current date?

The dates in the DateTable are only used for grouping and date intelligence. If you have the two columns related the value will be the same in both tables. If you have the date 1/1/2020 it will be related to 1/1/2020 in the DateTable. What you test in the if if the date is 1/1/2020:

IF(1/1/2020<=1/1/2020, Actual, Forecast)

or if the date is 5/5/2020

IF(5/5/2020<=5/5/2020, Actual, Forecast)

The tests will always return Actual.

If you have the relationships correct. You will have to use the Related function to reference a column in a related table.

I hope this makes sense.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Fri 28 Feb 2020, 11:06 Delegate Luca said...

RE: Referencing two tables

Hi Jens

Thanks for the reply.

I have created a column in my date table which contains the same period end date for every single row. This is essentially the monthly report date (which I will update every month).

So, what I want to do is test whether the dates associated with my marketing costs, are less than the "Report Date" in my Date Table.

Reason I need this if function is because I have an actuals column and a forecast columns, which both need to be merged into one to get a rolling actuals/forecast data point for the upcoming financial year.

This probably isn't the best way to do this, but couldn't think of an alternative. If you have an alternative solution then please advise.

Thanks,
Luca

Fri 28 Feb 2020, 19:05 Trainer Jens said...

RE: Referencing two tables

Hi Luca,

The data model in power bi controls how you have to write the DAX formulas. I will need to know how your table is related to the DateTable. Do you have a active relationship from your DateTable to the table where you want to test if it is less than your report date. Is the foreign key the Date column in your table or do you have other dates in the table?

If your relationship is from your primary key in the DateTable to the date you want to test in your Fact table (transaction table), the if below should work:

=IF([Date I want to test in fact table]<=Related('DateTable'[Repot Date]), Actual, Forecast)

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Fri 6 Mar 2020: Automatically marked as resolved.

Training courses

	98.7 % recommend us
	More live stats...

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

Power BI tip:

Master the Power BI Custom Visuals

Power BI offers a variety of custom visuals created by the community and Microsoft. Explore these visuals to enhance your reports and dashboards. Whether it's a custom map, a timeline, or a unique chart type, custom visuals can add significant value to your visualizations.

View all Power BI hints and tips