Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have two tables. Due to the sensitivity of the data, I am unable to share actual data. Let's assume two tables: Dates and Jobs. There is a realtionship between the tables based on the week date.
Dates stores a list of weeks (Monday) for approx 3 years. Each week has a percent complete that is anticipated.
Jobs stores all the jobs in the backlog. There are several categories of jobs. Let's assume there are 3000 total jobs in the table. A fresh "set" of jobs data is imported once a week. So we are building a history in the Power BI table. The datasource is an ever growing Excel spreadsheet (that's were the real history is being built). The data in Jobs is refreshed every week. A complete new set of rows is added for each week with updated status etc.
As an example, there could be 1000 total jobs to be completed based on in page filters. For the week of 06-Jan the plan would be to complete 5% of those jobs. The following week, the plan is to have completed 8% of those jobs, and so on until the total is 100%.
We only have data for 30-Dec-19 and 06-Jan-20. If no data is present for subsequent/future weeks, I need to use the total count (filters applied of course) of the most recent week's data. Take that and multiply by the given't weeks planned percent complete. I want to show number of jobs that need to be complete each week until the total is 1000 jobs (or whatever the filters determine). The total number of jobs (Target MAX) can grow or shrink from week to week.
I'm having a hard time creating the measure beyond the data in the Jobs table. In the table below, I need 1039 to show for all weeks past 6-Jan-20
Week | Cumulative Actual Count | Cumulative Plan Count | 4 - Target MAX |
12/23/2019 | 0 | ||
12/30/2019 | 0 | 0 | 850 |
1/6/2020 | 0 | 11 | 1039 |
1/13/2020 | |||
1/20/2020 | |||
1/27/2020 |
This is the measure that I'm using.
The data in the second column in original example (Cumulative Plan Count) is calculated by multiplying that week's decimal target by the total number of jobs for that week. I need that to be populated going forward as well.... beyond the weeks for which we have data...
An example of the Dates table. Multiply the total number of jobs for a given week by the decimal value in the second column of this table to determine the target number of jobs to complete each week.
WeekDate | 4 - Work Pkg Built | Week_BL_Key |
09-Dec-19 | 0.00 | 20191209 |
16-Dec-19 | 0.00 | 20191216 |
23-Dec-19 | 0.00 | 20191223 |
30-Dec-19 | 0.00 | 20191230 |
06-Jan-20 | 0.01 | 20200106 |
13-Jan-20 | 0.03 | 20200113 |
20-Jan-20 | 0.06 | 20200120 |
27-Jan-20 | 0.08 | 20200127 |
03-Feb-20 | 0.11 | 20200203 |
10-Feb-20 | 0.13 | 20200210 |
17-Feb-20 | 0.15 | 20200217 |
24-Feb-20 | 0.18 | 20200224 |
02-Mar-20 | 0.20 | 20200302 |
09-Mar-20 | 0.23 | 20200309 |
16-Mar-20 | 0.25 | 20200316 |
If possible... bonus points... would like to show 850 for all weeks prior to 30-Dec-19.
Use the "oldest" count for all weeks going backwards.
Use the "newest" count for all weeks going forwards.
HI @seaborne ,
I'm not so clear what the specific date you mean that used to split the oldest and newest targets, do you mean this date is selected from slicer? If this is a case, you can use aggregate functions(max, min...) or selectedvalue function to extract selected value from the calendar table and use it in calculation expression.
BTW, you need to use calendar date as the axis of visual, or your formula calculates with be filtered by 'auto exist' filter effect:
Understanding DAX Auto-Exist
Regards,
Xiaoxin Sheng
Hi @seaborne,
Since I not so clear for your data structure and relationship mapping, can you please share a pbix file with some dummy data and expected results?
Regards,
Xiaoxin Sheng
The data in the "Week" column in the example abouve is from the Dates table. The other columns are from the Jobs table.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |