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
Calculate result for next 4, next 10 and next 16 weeks based on month from matrix row
Based on the first column of the matrix (year and month hierarchy), i would like to create a measure that perform a calculation based on another measure. The second column "Calculation" is fine as it receives the date input from the date hierarchy. However, i struggle to perform the calculation for "Next 4" weeks (and also for "Next 10", "Next 16", etc.) The calculation "Next 4" is supposed to take the first date of the month, return the following 4 weeks (or 28 days), and evaluate another measure for the set of dates returned. In example, for january 2020, I want it to perform the calculation for the range between January 1st and January 28th. Similarly, the calculation "Next 10" is supposed to take the first date of the month, return the following 10 weeks (or 70 days), and evaluate another measure for the set of dates returned. In example, for january 2020, I want it to perform the calculation for the range between January 1st and March 11th. "Estimated revenue" is calculated in another measure, which works fine for the monthly date ranges provided by the table. Planned next 4 weeks (dynamic) = // Calculating estimated revenue for the next 28 days from the start of a given month. CALCULATE( [Estimated revenue]; DATESINPERIOD( Revenue[Date]; STARTOFMONTH(DateTable[Date]); 28; DAY ) )Dynamic Period Value comparison of last N Months
I basically need to compare enagaged value to last compare N months as period and assign a value. which technically should fall like this: Date Contact Engaged PreviousEngaged Status 01-04-2020 CON-12 1 02-04-2020 CON-13 0 03-04-2020 CON-14 0 04-04-2020 CON-15 0 05-04-2020 CON-16 0 06-04-2020 CON-17 0 07-04-2020 CON-18 0 08-04-2020 CON-19 1 09-04-2020 CON-20 0 10-04-2020 CON-21 1 01-05-2020 CON-12 1 1 Retained 02-05-2020 CON-13 1 0 Not Retained 03-05-2020 CON-14 1 0 Not Retained 04-05-2020 CON-15 1 0 Not Retained 05-05-2020 CON-16 1 0 Not Retained 06-05-2020 CON-17 0 0 Retained 07-05-2020 CON-18 1 0 Not Retained 08-05-2020 CON-19 0 1 Not Retained 09-05-2020 CON-20 1 0 Not Retained 10-05-2020 CON-21 0 1 Not Retained 01-06-2020 CON-12 1 1 Retained 02-06-2020 CON-13 0 1 Not Retained 03-06-2020 CON-14 1 1 Retained 04-06-2020 CON-15 0 1 Not Retained 05-06-2020 CON-16 1 1 Retained 06-06-2020 CON-17 0 0 Retained 07-06-2020 CON-18 0 1 Not Retained 08-06-2020 CON-19 1 0 Not Retained 09-06-2020 CON-20 0 1 Not Retained 10-06-2020 CON-21 0 0 Retained
Dynamic dimension filtering based on date slicer
I have an issue with a filter I need to implement in a Power BI dashboard in a real estate environment The Power BI Dashboard is connected to Azure Analysis Services in Live Connection Mode, therefore the model and measures are in AAS. The so called 'In Standing' filter must be a slicer or bookmarkfilter that filters the Rental objects that are in exploitation at the start of a selected time period (selected using a date slicer). Whether a rental object is in standing is determined by its 1st contract date which is a column in the DimVastgoedobject (the rental object. The in standing filter must filter a matrix that has the number of Rental objects, Hire Rate, Abandon Rate sliced over dimRegion, dimComplex. This I managed to implement using a filter measure: InstandingFilter := VAR EersteVerhuurdatum = MIN(dimVastgoedObject[dimVastgoedobjectEersteContractdatum]) VAR BegindatumPeriode = CALCULATE ( MIN(dimDate[DateDate]); VALUES(dimDate[DateDate]) ) RETURN IF( EersteVerhuurdatum <= BegindatumPeriode; 1;0 ) However if I want to do a trend analysis over time in a linechart then filtering using Instandingfilter = 1 doesn't work. Any ideas how to make this work?
How to get sum of units in user-defined date period?
Hi, you guys have helped me out before, hoping you can do so again! 😊 I have a table that contains the following fields: ClientID Supplier StartDate EndDate WeeklyUnits 1 ACMECo 06/06/2019 10 2 BillyOnions 01/01/2019 17/01/2019 5 3 FredBloggs 01/02/2019 03/03/2019 10 4 ACMECo 31/12/2018 12/05/2019 20 I have a DateKey table where I have NOT created a relationship to any of the date fields in other tables. I use a date slicer in the report to produce a graph of units per week by supplier. This is used for giving a trend on what a provider’s planned unit capacity was at the beginning of each week. To get this I use the following measure in conjunction with filtering the visual to just show data for Mondays: WeeklyUnitSnapshot= CALCULATE( SUM('MyTable'[Weekly Units]), FILTER( 'MyTable', [START DATE] <= FIRSTDATE(DateKey[Date])), FILTER( 'MyTable', [END DATE] = BLANK() || 'MyTable'[END DATE] >= LASTDATE(DateKey[Date])) ) So far, so good, but I also need to track what was actually delivered by suppliers in the date period defined by the report user. eg. "How many units did AcmeCo provide between 1st of Jan and 1st of Feb?" What I need to achieve is calculate the number of days the order was open within the user selected dates, taking into account the start and end dates in the table, then multiplying the number of days by weekly units and then dividing by 7. I’ve tried various ideas but can’t fathom how to get this working, I tried another measure but it was ignoring the user-selected date range and just using the first and last dates in my DateKey table. Any help would be greatly appreciated!
