CODEWITHSUNDEEP
excelpowerbidaxpowerquerypowerbi-desktop
Anita
Anita

Reputation: 13

How to calculate monthly variance in Target Table

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

Upvotes: 0

Views: 409

Answers (1)

Murray Foxcroft
Murray Foxcroft

Reputation: 13745

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.

Upvotes: 0

Related Questions