InOut report

[miemer]

Hi everyone, I'm new to power bi even though I've built several reports for work. Now I have this problem: I would like to build a table in which the first column is the date while the second column is populated by taking values ​​from two tables (sql and Filemaker via odbc). The data from sql are negative, the consumption of a raw material while the data from Filemaker are the income of that matter (positive values). I would like to represent the entire progression of consumption by showing the trend to understand if I will have problems before the expected date of receipt of the goods...

Thanks

[v-yangliu-msft · Accepted solution]

Hi  @miemer ,

Sumx returns the sum of the expressions evaluated for each row in the table.

You can also use these methods to achieve the desired effect:

According to your description, I create this data:

Table Filemaker:

Table SQL:

Here are the steps you can follow：

Associate the three tables with the date column:

The first method:

Create calculated column.

trend_way1 =
var _sql=CALCULATE(SUM('SQL'[Consumption of raw materials]),FILTER('SQL','SQL'[date]=EARLIER('Table'[date])))
var _File=CALCULATE(SUM('Filemaker'[Material income]),FILTER('Filemaker','Filemaker'[date]=EARLIER('Table'[date])))
return
_sql+_File

The second method:

Create calculated column.

trend_way2 = RELATED(Filemaker[Material income])+RELATED('SQL'[Consumption of raw materials])

Result：

Finally put it into the line chart for display

You can downloaded PBIX file from here.

Here is the link of the Dax function, I hope it will help you:

https://docs.microsoft.com/en-us/dax/related-function-dax

https://docs.microsoft.com/en-us/dax/sumx-function-dax

Best Regards,

Liu Yang

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

[AllisonKennedy]

I would say you'll need three tables in your PBI data model: DimDate, SQL and Filemaker

Relate the SQL and Filemaker to DimDate using Date or DateKey columns. 

https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html

Then you can create a table visualization with DimDate[Date] as the first column. Create a measure for the second column. New MEASURE:

StockOnHand= SUMX(DimDate, CALCULATE(SUM(SQL[Consumption])) + CALCULATE(SUM(Filemaker[Income])) )

Then use this in a line chart and use the forecast analytics to see trends. 

[miemer]

Thanks for the valuable reply, I will try the proposed solution as soon as possible. To take advantage: The SUMX function will retrieve the values ​​from the two tables (sql and filemaker) according to the date in the first column, correct?