Consolidated running balance from two fact tables
I have this simple data model w/ two fact tables that pertain to purchases and sales of shares of stock: I need to calculate a running balance by taking a running sum of shares purchased by ticker and SUBTRACT by the running sum of shares sold by ticker. I tried this: Shares balance 1 = VAR Date_Ref = MAX( dDates[Date] ) VAR Tick_Ref = VALUES( dAssets[Ticker] ) VAR Cumm_Purch = CALCULATE( SUMX( fPurch, fPurch[Shares] ), fPurch[Ticker] = Tick_Ref, fPurch[Date] <= Date_Ref ) VAR Cumm_Sold = CALCULATE( SUMX( fSales, fSales[Shares] ), fSales[Ticker] = Tick_Ref, fSales[Date] <= Date_Ref ) VAR Result = Cumm_Purch - Cumm_Sold RETURN Result And I also tried this: Shares balance 2 = VAR Date_Ref_Purch = MAX( fPurch[Date] ) VAR Tick_Ref_Purch = VALUES( fPurch[Ticker] ) VAR Cumm_Purch = CALCULATE( SUMX( fPurch, fPurch[Shares] ), fPurch[Ticker] = Tick_Ref_Purch, fPurch[Date] <= Date_Ref_Purch ) VAR Date_Ref_Sold = MAX( fSales[Date] ) VAR Tick_Ref_Sold = VALUES( fSales[Ticker] ) VAR Cumm_Sold = CALCULATE( SUMX( fSales, fSales[Shares] ), fSales[Ticker] = Tick_Ref_Sold, fSales[Date] <= Date_Ref_Sold ) VAR Result = Cumm_Purch - Cumm_Sold RETURN Result And both produce the same WRONG results, as shown on the sample table that follows... For this visual the 'Ticker' column comes from the dimension table dAssets and the 'Date' column comes from the date table. And the red column I manually added to show the numbers I was supposed to compute: Ticker Date Shares purchased Shares sold Shares balance 1 Shares balance 2 Correct result ABEV 3/1/2021 77 77 77 77 ABEV 5/4/2021 73 73 73 150 ABEV 6/4/2021 150 -150 -150 0 ABEV 6/28/2021 62 62 62 62 ABEV 10/29/2021 62 -62 -62 0 ALSO3 1/7/2021 39 39 39 39 ALSO3 2/3/2021 39 -39 -39 0 ALSO3 3/1/2021 45 45 45 45 ALSO3 5/11/2021 45 -45 -45 0 ALSO3 6/29/2021 36 36 36 36 ALSO3 1/7/2022 56 56 56 92 ALSO3 3/30/2022 92 -92 -92 0 I suppose something on either code is messing up the results, what could that be?
Running balance ignoring other columns
Hi, I am new to DAX and have been looking for an answer for about a week now and I cant get it right. The scenario is simple. I need a running total to create a balance that keeps on running, independant of other columns. So it just has to keep adding the previous row to the current row. The problem is with the product category column, that creates a group on which the RT is based on. I don't want that. the DAX for "cumulative balance" = cumulative balance:=CALCULATE([TotalRevenue], filter(ALLEXCEPT(dDate,dDate[Year],dDate[Month Number]), dDate[Date] <= MAX(dDate[Date]) )) The data model is classic star schema with Sales fact , date dimension, product dimension. How to ignore the product dimension and have the running total keep on totalling?
How to have a running balance of distinct counts based on different column dates
Hi, I have a transaction table that has a start date and completion date columns. I need to determine the count of OPEN transactions per month. A transaction is considered open for a reference month if its start date is on or before that month, AND ( Completion date is either blank, OR its month is after the reference month). I have to have it in such a way, so when I plot its graph, report filters using the transaction table columns are still applicable. For example: the first table shows the data set, and the second table shows the expected Open ItemRunning Balance. The challenge I have is to come up with the correct measure so that it is report filterable on the fly. Thank you in advance.Running balance with no dates
I want the 'balance' column to decrease with each invoice. I've tried altering codes for running totals and have gotten nothing except an increased list with each invoice being duplicated many times. My data is just an excel spreadsheet...2 of them actually. 'Total This Invoice' and 'Revised Amount' are from different tables (with a relationship based on the specific project). I think it should be as basic as: Running Balance = (sum(CommitmentsSC_SummaryLog[Revised Value])-sum('Vanir - Commitment Invoices'[Total This Invoice])). I tried that, but that is what duplicates the rows over and over again... I don't know if it effects the question at hand, but I do have two slicers that filter this visual. Here is what I haveas a visual:
