How to calculate monthly variance in Target Table

Question

I need to calculate multiple KPIs and make visualizations for different financial years in Power BI Dashboard. Most of the target data (annual and monthly) is stored in spreadsheet. Would like to design and store target /goals in power BI data model, how to create a calculated column in power query to define cumulative target variance across months by taking annual baseline value.

Not sure if Lookup function exists in Power BI DAX and how to work it out?

Thanks, Anita

What have you tried so far? Can you post up some code that you have tried? — Murray Foxcroft

Murray Foxcroft Murray Foxcroft · Accepted Answer · 2020-02-02T13:12:57

PowerQuery / M is not the right place for running totals. This is best left to DAX.

There are may ways of dealing with cumulative totals in DAX, you need to use a measure as opposed to a calculated column.

DAX Patterns has a simple example

Cumulative Total:=
CALCULATE (
    SUM ( Transactions[Value] ),
    FILTER (
        ALL ( 'Date'[Date] ),
        'Date'[Date] <= MAX ( 'Date'[Date] )
    )
)

Comparing Period on Period can be done with PREVIOUSMONTH, PREVIOUSQUARTER, PREVIOUSYEAR

To you question on lookups, PowerbI does have LOOKUPVALUE.

Your answer will likely be a combination of the above, keep it simple and build up individual measures to answer each piece and then combine them together.

How to calculate monthly variance in Target Table

1 Answers