Paginated Budget Report - Monthly and Cumulative YTD Totals in same Matrix

[bigrods]

Hi,

I'm just asking to enquire if it's possible for Power BI Report Builder to create a financial Budget Report with one matrix that lists a mix of both monthly Actual/Budget data AND cumulative YTD Actual/Budget data.

I have had a brief play around with this, but the issue is, if I set either filters or parameters in the report then the Matrix will either filter ALL the data just by the specific month OR it will give me all the YTD data so I cannot acheive a mix of displaying Monthly  Budget/Actual Totals and Cumulative YTD Budget/Actual Totals.

I wondered if anyone had come across this before and is it possible for some i.e. YTD fields to bypass the Monthly filters and display as a cumulative total?

Many thanks for any assistance!

[vivien57 · Accepted solution]

Hello @bigrods , 

I think it's possible, but It depends on the implementation. 

There are several reliable approaches to display in the same matrix:

• Monthly totals (Actual/Budget for the selected month)
• Cumulative YTD totals (Actual/Budget from the start of the year up to the selected month)

 Power BI Report Builder, but you need to avoid having your month parameter filter all the data that your YTD calculation depends on.

Idea: Provide the report with all months of the year and apply the month filter only to display, then calculate YTD with RunningValue in a Year group scope.
If you filter before calculation (e.g., at dataset level), RunningValue will only sum remaining rows. Keep all year rows in the data region.

Setup :

• Dataset: do not filter by @pMonth (filter only by @pYear)
• Matrix groups: YearGroup → MonthGroup
• Display filter: on MonthGroup (show only =Parameters!pMonth.Value)

' Monthly
=Fields!ActualAmount.Value
=Fields!BudgetAmount.Value

' YTD (cumulative up to displayed month)
=RunningValue(
    Fields!ActualAmount.Value,
    Sum,
    "YearGroup"
)
==RunningValue(
    Fields!BudgetAmount.Value,
    Sum,
    "YearGroup"

YTD ignores dataset filter (since not applied) and calculates correctly in the Year group even if you display only one month.

That's how I handled a similar case.

Have a nice day, 

Vivien

[bigrods]

Hi,

I managed to get this working, thank you! 

I removed any date-related filters on my matrix then had the "Annual" columns as the measures from my DataSet (imported from PowerBI).

For the "Monthly" columns, I created a report Parameter, based on each month in the reporting year then created the Monthly columns manually as Expressions, but referring to the Parameters date period.

Thanks so much!