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
Creating Visual based on running weekly values
Hi all, My brain is pretty much fried trying to think of the logic behind this so hoping somebody may have done something similar or have a good idea how to create the below chart as a chart within Power BI. The above table is currently manually put together within excel. I am trying to replicate it within a report so the manual aspect is taken away. What the business is looking for is a chart which calculates the value of invoices processed during the week (showing as the Actuals per week), this week approvals are jobs awaiting to be invoices and the targets are static. I "currently" don't have an issue actually calcualting the data as I have everthing set to put together. So my question is to see if it is possible to automate this. Is there a way, where if a month was select from a silcer, the weeks within that month are shown (can just be numbered or dated) within the chart and the totals are displayed. For example from above, for Area 1, week 5 of august will be shown tomorrow automatically? Then next month September, week 1 automatically calculate the totals with any invoices created then move onto week 2 after next friday. I originally thought that using CALCUATE based on the current week then -1 week, then -2 etc on the total, it would allow me to get the what I need but this would only allow the report to be run at the end of the month. Is there a way to base the calcualtion on the year week or month week. Apologises if this doesn't make too much sense, as mentioned, I've been looking at this for pretty much 6 hours straight and I've just kept going round in circles. Any help would be greatly appeciated! Thanks!
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
Current weekly averages as compared to years past weekly averages
Hello, I am working with a large, police-data set looking at searches. The data set spans over 5 years, and there are duplicate items (example: if two subjects were apart of one search, they will have the same item number). I am able to create a measure that counts those individual items, but what I am trying to do is create a line and stacked column chart with the column showing the week-to-date total of searches, and the line showing the average from the weeks from the previous years (example: it is week 6 of 2019, so the column may show 40 searches that have happened thus far, and the line will show the running average of week 6 according to the data from 2014-2018). I cannot use the individual count measure that I made in this DAX expression. AvgPerWeek = CALCULATE(AVERAGE('Stop and Search'[Week Mon-Sun]),FILTER(ALL('Stop and Search'), 'Stop and Search'[item number this is where I would have liked to use the measure for individual count] =MAX('Stop and Search'[item number this is where I would have liked to use the measure for individual count]))) Any help you could give would be awesome.
