Cumulative Totals for Balance Sheet

[AlexDawson111]

Hi,

Im looking for some advice as i have become stuck on a task regarding balance sheet measures.

The Task


I need to create a balance sheet that just looks at debit amounts. The visual needs to be in a Matrix Table with ROWS as my Account Number and COLUMNS as my Month Name and VALUES of the CUMULATIVE Debit amount.

The user needs to be able to select multiple months in the slicer selection. I will have two slicers one called Month Name and on called Year. These will both come from Dim_Calendar.

The Model

Focusing on the parts of the data model that i will be using are as follows.

FACT_Amounts = This is where the debit amounts sit under a field called Amount
DIM_Calendar = This is my date table that has a one to many relationship with my FACT_amounts table (Date to TransactionDate)
DIM_Accounts = This is where the account numbers come from and has a one to many relationship with FACT_amounts.

I think i may need to adopt some sort of disconnected calender and measure to achieve this as currently when i select a value from my slicer this is affecting the values in the matrix. 

Thanks

[AlexDawson111]

To give some further info. This is the running total of the accounts when no slicer is in use and no date fields used in the matrix:-

When i place slicers selecting March and 2024 for example this is the result which is incorrect:-

The measure in use:-

CALCULATE(

  [Debit Amount],

  FILTER(

    ALL(Dim_Calendar[Date]),

    Dim_Calendar[Date] <= MAX(Dim_Calendar[Date])

  )

)

[v-zhengdxu-msft]

Hi @AlexDawson111 

Actually that I haven't understand what you are expression.

Please share your data(exclude sensitive data) or create some sample data with Text format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following links to share the required info:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.