Re: Measure to store the most recent (by date) cou...

seaborne · ‎01-09-2020

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.

4 - Target MAX = CALCULATE (

COUNT( 'Jobs'[4 - Work Pkg Built]),

FILTER('Jobs, OR(Jobs[4 - Work Pkg Built]="YES", Jobs[4 - Work Pkg Built]="_")),

FILTER (

ALL ( 'Jobs'[Week_Imported] ),

'Jobs'[Week_Imported]= 'Dates'[Max WBS_Export Date]

))

seaborne · ‎01-09-2020

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...

seaborne · ‎01-09-2020

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

seaborne · ‎01-09-2020

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.

v-shex-msft · ‎01-10-2020

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

seaborne · ‎01-10-2020

Yes. Using a slicer to select dates. But also want to be able to draw the visual showing all dates in the table. All weeks.

The problem is that the Jobs table only has a limited data set. 2 weeks currently. We need to forecast out a calculated job count per week that extends well beyond the weeks in the Jobs table. The problem is that the rows beyond what weeks are in the Jobs table are blank.

seaborne · ‎01-10-2020

The “week” column in the original / top example is the axis of the visual. This is a calendar date from the Dates table. There is a slicer to optionally narrow the range of dates displayed in the visual

seaborne · ‎01-10-2020

The dates that determine old/new are the available weeks in the Jobs table. We import a complete week of jobs data week over week. We have a set of data for 30-dec and 06-Jan.