Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago

How to reference a query (table)

Hi, I have the following query which is also a table. I am thinking to use CALCULATE as a measure in Power BI report, but I don't know how to reference different row in the query by situation?   ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
1 year ago

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,

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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's avatar
      Anonymous
      Not applicable
      1 year ago

      Anonymous 

       

      Very interesting, you use EARLIER function which I have not learnt it yet. Thanks for your help.