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,

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.