Forum Discussion

miemer's avatar
miemer
Regular Visitor
5 years ago
Solved

InOut report

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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's avatar
AllisonKennedy
Community Champion
5 years ago

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's avatar
miemer
Regular Visitor
5 years ago

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?