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
rajendraongole1's avatar
rajendraongole1
Super User
1 year ago

Hi Anonymous  - you can structure your measure for "2025 Control Budget"

 

2025 Control Budget =
VAR SelectedForecast = SELECTEDVALUE( 'YourTable'[Forecast] )
RETURN
CALCULATE(
SUM( 'ActualCostTable'[Cost] ),
'ActualCostTable'[Date] <= DATE(2024,12,31)
) +
CALCULATE(
SUM( 'ForecastCostTable'[Cost] ),
'ForecastCostTable'[Date] >= DATE(2025,1,1)
)

 

replace with your table name, or the other measures (Prev Month -1 Forecast HCC, Prev Month Forecast HCC, Working Forecast), you can create similar measures but adjust the date filters accordingly.

 

Hope this helps.

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi, rajendraongole1:

 

Thanks for your help. Appreciated!

I am wondering if the highlighted yellow dates can be directly referenced to the query above from different rows, which I shared earlier based on which measure I set up. 

 

 

    • 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