Search code examples
sqlpowerbipowerbi-desktoppowerbi-datasourcepowerbi-paginated-reports

PBI paginated SUM initial opening balance only

I'm trying to recreate a ERP system report within power BI/paginated. However I'm really struggling with calculating the opening balance group totals for this report, as there is a opening balance for each month within the date range, and when I come to the header total I need to sum only the initial opening balances at the beginning of the date range for each fixed asset (the visible output basically). How can this be achieved?

Desired Output

Desired Output

Actual Output enter image description here

Grouping simply fixedassetnumber at detail and parent group by asset group.

enter image description here

Can provide data if needed.

Solution

  • I actually had a eureka moment and found the answer myself. In the total header I created an expression =Sum(IIf(Month(Parameters!FromAssetTransactionDate.Value) = Month(Fields!AssetTransactionDate.Value), Fields!OpeningBalance.Value, 0)) basically if the transaction date equals falls in same month as my from parameter, this will only total the 'initial opening balance' for the range, logic seems sound.