Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
AlexDawson111
Frequent Visitor

Cumulative Totals for Balance Sheet

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

2 REPLIES 2
AlexDawson111
Frequent Visitor

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:-

AlexDawson111_0-1716551193877.png


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

AlexDawson111_1-1716551270087.png

 

The measure in use:-

CALCULATE(
  [Debit Amount],
  FILTER(
    ALL(Dim_Calendar[Date]),
    Dim_Calendar[Date] <= MAX(Dim_Calendar[Date])
  )
)




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.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.