POWER BI 計算與上個月的差異 (數值前後比較)

Thanks for sharing the details, annie! So your goal is for each metric (e.g., TEU, REV TTL, REV AVG, etc.), you want:

• Current Month Value
• Previous Month Value
• Difference (Current – Previous)
• Difference % (Current / Previous – 1)

First you need a date column,

Date =

ADDCOLUMNS (

CALENDAR (DATE(2020,1,1), DATE(2030,12,31)),

"Year", YEAR([Date]),

"Month", MONTH([Date]),

"MonthName", FORMAT([Date], "MMMM"),

"YearMonth", FORMAT([Date], "YYYY-MM")

)

Mark this table as Date table. If you already have one, just skip this.

Then create a measure for TEU, assuming your fact table is Voyages:

TEU = SUM(Voyages[TEU])

Now use DATEADD to shift 1 month back:

TEU Prev Month =

CALCULATE(

[TEU],

DATEADD('Date'[Date], -1, MONTH)

)

This gives the October TEU when you are viewing November.

Now you can calculate TEU Month-over-Month Difference and TEU Month-over-Month % Difference

TEU MoM Diff = [TEU] - [TEU Prev Month]

TEU MoM % = DIVIDE([TEU] - [TEU Prev Month], [TEU Prev Month])

Now for the other metrics, just repeat it

Example for REV TTL:

REV TTL = SUM(Voyages[REV TTL])

REV TTL Prev Month =

CALCULATE([REV TTL], DATEADD('Date'[Date], -1, MONTH))

REV TTL MoM Diff = [REV TTL] - [REV TTL Prev Month]

REV TTL MoM % = DIVIDE([REV TTL] - [REV TTL Prev Month], [REV TTL Prev Month])

Example for REV AVG:

REV AVG = AVERAGE(Voyages[REV AVG])

REV AVG Prev Month =

CALCULATE([REV AVG], DATEADD('Date'[Date], -1, MONTH))

REV AVG MoM Diff = [REV AVG] - [REV AVG Prev Month]

REV AVG MoM % = DIVIDE([REV AVG] - [REV AVG Prev Month], [REV AVG Prev Month])

If you want a “Before” and “After” column directly, use this:

Before TEU = [TEU Prev Month]

After TEU = [TEU]

See if this helps. If you need further assistance just let me know.

Best regards,

Kimberly