Basic Power BI DAX Measures

Organising your model

Below are standard measures to use in your Power BI to do useful things like compute Month-on-Month or Year-on-Year calculations for any kind of metric.

It assumes you have a model set up with one (or more) Fact Tables (i.e. tables where you store your actual data, such as revenues, sales, connections, etc.

And have connected those to proper Dimension Tables (i.e. tables with unique values for Customers, Countries, Dates, Products) which you use to connect your reference data for those dimensions to all your fact tables.

Example: USA is in North America, so your Fact Table would have:
USA | May-23 | 1,000
and your Dimension Table would have:
USA | North America

This “Star Schema” way of organising your data leads to improved performance & much easier calculations for your measures & columns.

YoY & previous year measures for use for use in Power BI

Generic YoY one, here for Revenues.

# Revenue YoY = 
VAR CurrentX = [# Revenue Sum]
VAR PreviousX= [# Revenue Sum PY]
VAR DeltaX = CurrentX - PreviousX
    IF (
        NOT ISBLANK ( PreviousX ),
        CurrentX - PreviousX
    )
VAR ResultX =
    IF (
        NOT ISBLANK ( CurrentX ),
        MIN(MAX(DIVIDE ( DeltaX, PreviousX, 0) *SIGN(PreviousX),-9.99),9.99)
    )
RETURN DeltaX

For YoY %, much the same can be used, but then returning ‘ResultX’.

# Revenue YoY % = 
VAR CurrentX = [# Revenue Sum]
VAR PreviousX= [# Revenue Sum PY]
VAR DeltaX = CurrentX - PreviousX
    IF (
        NOT ISBLANK ( PreviousX ),
        CurrentX - PreviousX
    )
VAR ResultX =
    IF (
        NOT ISBLANK ( CurrentX ),
        MIN(MAX(DIVIDE ( DeltaX, PreviousX, 0) *SIGN(PreviousX),-9.99),9.99)
    )
RETURN ResultX

In case you need it, here’s a simple measure to calculate the Previous Year of any measure. Make sure you have a date table, like the one I wrote about in my previous post.

# Revenue Sum PY = 
CALCULATE (
    [# Revenue Sum],
    CALCULATETABLE (
        SAMEPERIODLASTYEAR ( 'Date Table'[Date] )
    )
)

# Revenue Sum = SUM('Data Revenue'[Revenue])

Month on Month DAX for Power BI

# Revenue MoM = [# Revenue] - [# Revenue P1M]

# Revenue P1M = 
/* Revenue for 1 month prior = the revenue for the previous month */
CALCULATE (
    [# Revenue],
        PARALLELPERIOD( 'Date Table'[Date],-1,MONTH)  
    )