How to reference a query (table)

[TANA]

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?

For example, when I create a measure called "2025 control budget", I would like combine two set of data, Actual cost data will come from a query which any cost before and include 2024 Dec data, then forecast cost data will come another query which will include cost info from Jan 2025 and onward. I plan to create four measures as listed under Name column.

Can you please provide me some suggestions? Thanks,

[rajendraongole1]

Hi @TANA  - 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.

[TANA]

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. 

[v-shex-msft]

HI @TANA,

You can put this part into variable and use if statement or switch function to handle and return different results based on calculation groups.

Using calculation groups to selectively replace measures in DAX expressions - SQLBI

Create calculation groups in Power BI - Power BI | Microsoft Learn

Regards,

Xiaoxin Sheng

[TANA]

Hi, @v-shex-msft:

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,

[v-shex-msft]

HI @TANA,

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