Last Twelve Months Actuals v Budget

Hi Carrie,

Thank you for the forum question.

You’re very close — the issue you’re hitting is a classic Power BI/DAX time intelligence pitfall.
Root cause (why it works in separate tables but not together)
Your measures are using:
DATESINPERIOD(
Tbl_MAsForecast[Month],
LASTDATE(Tbl_MAsForecast[Month]),
-12,
MONTH
)
That works only when the visual is already filtered by that table’s own date column.
When you put Month from the Date table on the visual:
• LASTDATE(Tbl_MAsForecast[Month])
• LASTDATE(Tbl_MAsForecastBudget[Month])
Do not respond to the Date table filter properly
So:
• Each fact table calculates its own independent “last date”
• The rolling window is no longer aligned
• Hence incorrect LTM values when combined
________________________________________
Correct pattern: Time intelligence must use the Date table
Once you have a proper Date table related to both fact tables, all time filtering must be driven by the Date table only.
Assumptions
• Date[Date] is a continuous daily date column
• One to many relationship from Date[Date] → each fact table date column
• Date table is marked as Date table in Power BI
________________________________________
Correct LTM measures
LTM Actuals
LTM Live :=
CALCULATE(
SUM(Tbl_MAsForecast[Value]),
DATESINPERIOD(
'Date'[Date],
MAX('Date'[Date]),
-12,
MONTH
)
)
``
LTM Budget
LTM Budget :=
CALCULATE(
SUM(Tbl_MAsForecastBudget[Value]),
DATESINPERIOD(
'Date'[Date],
MAX('Date'[Date]),
-12,
MONTH
)
)
Key change:
DATESINPERIOD now references 'Date'[Date], not the fact table month.
________________________________________
Why this works
When you use:
• Month from the Date table in your visual
• MAX('Date'[Date]) as the anchor date
Power BI evaluates the same rolling 12 month window for both datasets, allowing them to be shown correctly together.
This is exactly how Power BI’s time intelligence engine is designed to work.
________________________________________
Visual setup (important)
In your table or matrix:
• Rows:
'Date'[Month Year] (or formatted month)
• Values:
LTM Live
LTM Budget
Do not use month fields from fact tables.
________________________________________
Common checks if it still looks wrong
1. Relationship direction
o Date → Fact tables (single direction)
2. Granularity mismatch
o If fact tables store only Month (no day), use a proper month date:
 e.g. first day of month in both tables
3. Date table continuity
o No gaps in dates
o Covers the full date range of both datasets
________________________________________
Bonus: Cleaner alternative (recommended)
Power BI also supports:
LTM Live :=
CALCULATE(
SUM(Tbl_MAsForecast[Value]),
DATESINPERIOD(
'Date'[Date],
MAX('Date'[Date]),
-1,
YEAR
)
)
Functionally identical, clearer intent.
________________________________________
Summary
Always use the Date table in time intelligence functions
Never reference fact table date columns in DATESINPERIOD
This enables combining Actuals & Budget correctly in one visual



Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector