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!Previous week sales out of weekly data table
Hi, i have this data table of WEEKLY sales (by week number): i have a measure of Sales = sum(Quantity), i wish to create : 1. a measure of sales for the previous week (please notice the situation that the previous week for week1 is week52/53 of the previous year). 2. a measure of average sales UNTIL the previous week (exc. the current one) thanks in advance, Amit
Show Week Data for the last 8 weeks and display the average of the 8-weekly data
Hello everyone, I need some help and I would really, really appreciate any inputs. Desired Output: Dynamically show the weekly data for the last 8 weeks (including current selected week) and then calculate 8 week average based on the current selected week. Example: If I select 3/12/2021 (Corresponds to Week 13), table will show the Sales on 3/21/21 and the weekly sales in the last 7 weeks. Sample Output: I have 2 tables Sales_Fact and dimWeek joined by Closing Week. What I did is create 8 different measures: Current Wk, Last_Wk1, Last_Wk2, Last_Wk3, Last_Wk4, Last_Wk5, Last_Wk6, Last_Wk7 Last_Wk1 = var selectedwk = max(dimWeek[Week_No]) var wk = calculate(selectedwk-1) return calculate([NB_PL_CY],all(dimWeek[Week_No]),filter(all(dimWeek),dimWeek[Week_No]=wk)) Then I created Avg_8_Wk measure that average all those 8 measures. This is the result: This shows the last 8 weeks Sales, however, I don't think this is the right way to do it and also I would like to be able to see the Week_Ending date that dynamically change instead of the measure name (Please see sample output above). Would you be able to help? Thanks so much in advance! Best, Newbie_2020
