Weekly closingBlance by AccountNo with missing WeekEndDate
Hi everyone, I need help in generating a matrix/bar chart with weekly closing balance. I have two datasets : Position Date Date has been created based on "AccountingDate" of Position table. I added a column "WeekEndDate" which is the end date of each week. I tried to get the last BalanceValue for each "AccountNo" at the end of the week. The problem is that sometimes, AccountNo has no record in a week. Thus value in matrix is wrong. Here is an example : If my WeekEndDate is 14/01/22, final week balance should be : 95808,99 + (-1646984,4056) + 91288,25 = -1459887,17 But in my matrix Balance of AccountNo 512411 was not took in the measure. Hence It returns : (-1646984,4056) + 91288,25 = -1555696,16 I currently use this method I found from another forum and tuned it with my dataset : lastnonblankdate = LASTNONBLANK ( 'Positions et mouvements bancaires réels'[Accounting Date], CALCULATE ( SUM ( 'Positions et mouvements bancaires réels'[BalanceDaily] ) ) ) lastnonblankvaue by category: = VAR _lastnonblankdatebycategory = ADDCOLUMNS ( SUMMARIZE ( 'Positions et mouvements bancaires réels', 'Mapping comptes'[Accounting No] ), "@lastnonblankdate", [lastnonblankdate] ) RETURN IF ( HASONEVALUE ('Date'[Week End Date]), CALCULATE ( SUM ( 'Positions et mouvements bancaires réels'[BalanceDaily] ), TREATAS ( _lastnonblankdatebycategory, 'Mapping comptes'[Accounting No], 'Date'[Date] ) ) ) Do somoes ever faced this issue and came up with an alternative or solution. Thank you!
