Forum Discussion
How to reference a query (table)
Hi, Anonymous:
Thanks for your reply and help. Appreciated! Would you mind helping me further?
My situation is like this:
I have the following three queries for which, one side has the actual up to date cost data, and the other has forecast and budget cost data. Both data sets has similar structure.
Both Actual and Budget forecast queries have the following similar content.
Then I have the third query which was shared earlier.
Eventually, I would like to have a report which has a field parameter of 2025 control budget, working forecast, prev month's forecast or prev prev month's forecast. And the report will be a matrix with funding code as row header, and time as column header.
I was thinking about using CALCULATE as discussed earlier. would you mind giving me more directions on how? or is there any other methods to achieve the same result? please let me know. Thanks,
HI Anonymous,
So you mean you just want to summary records from two table based on current column values?
If that's the case, you can add calculated columns to use current Name and date to look up correspond table records.
Actual =
CALCULATE (
SUM ( ActualTable[amount] ),
FILTER (
ActualTable,
[version_name] = EARLIER ( SummaryTable[name] )
&& [month_number] = EARLIER ( SummaryTable[actual] )
)
)
Forecast =
CALCULATE (
SUM ( Forecase[amount] ),
FILTER (
ActualTable,
[version_name] = EARLIER ( SummaryTable[name] )
&& [month_number] = EARLIER ( SummaryTable[forecast] )
)
)Regards,
Xiaoxin Sheng
Anonymous
Very interesting, you use EARLIER function which I have not learnt it yet. Thanks for your help.