cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
seaborne
Helper I
Helper I

Measure to store the most recent (by date) count of a filtered total

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

 

WeekCumulative Actual CountCumulative Plan Count4 - Target MAX
12/23/20190  
12/30/201900850
1/6/20200111039
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]
))

 

9 REPLIES 9
seaborne
Helper I
Helper I

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
Helper I
Helper I

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.

 

WeekDate4 - Work Pkg BuiltWeek_BL_Key
09-Dec-190.0020191209
16-Dec-190.0020191216
23-Dec-190.0020191223
30-Dec-190.0020191230
06-Jan-200.0120200106
13-Jan-200.0320200113
20-Jan-200.0620200120
27-Jan-200.0820200127
03-Feb-200.1120200203
10-Feb-200.1320200210
17-Feb-200.1520200217
24-Feb-200.1820200224
02-Mar-200.2020200302
09-Mar-200.2320200309
16-Mar-200.2520200316
seaborne
Helper I
Helper I

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

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

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.

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

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.

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

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

The data in the "Week" column in the example abouve is from the Dates table. The other columns are from the Jobs table. 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors