Conditional logic to CALCULATE() filter argument???

[hrvat2]

Hello Forum!

I am relatively new to the BI stuff but have really gotten the bug as of late and have been immersing myself in a wide range of material. That is how I found my way here.
I am currently working on a model trying to generate a report that reflects some basic metrics between data sets containing date values. My question essentially has to do with implementing a conditional filter in a CALCULATE() function. To provide a bit of context, the following is a snapshot of the model diagram view:

https://drive.google.com/open?id=0Bx-m_o41YsVjVDRHc3hPYnpNWm8

A snapshot of the dimension/lookup table:

https://drive.google.com/open?id=0Bx-m_o41YsVjUm1YcDNKZ0wtMU0

Draft report:

https://drive.google.com/open?id=0Bx-m_o41YsVjNjZWeWQ5TExQUm8

And finally, the code for the couple of versions of the measure "Finish Var to 9/5/11" I am attempting to generate:
Version 1:

https://drive.google.com/open?id=0Bx-m_o41YsVjc1IzTXVhZ18zak0

Version 2:

https://drive.google.com/open?id=0Bx-m_o41YsVjX1F0WTFkT3BuaVE

Notes on version 1 of the measure ([Finish Var to 9/5/11]):

1. Wrapped in an IF statement so as to not reflect values on the system level (system example = 6-72C75-10, see Report excerpt link above), am only wanting to show values on schedule version basis (e.g. "DD 9/5/2011", "DD 10/3/2011" etc.)
2. The result-if-true portion of the above broken down as follows:
* [Finish Date] measure defined as:
Finish Date:=IF(HASONEVALUE('d Schedules'[Schedule Designation]),LASTDATE('ALL Sched NOC Data'[Finish]),BLANK())

'ALL Sched NOC Data' is the fact table, and the [Finish] column therein contains date values.

*I am trying to have the report reflect Finish date differences to the earliest schedule in the data set ("DD 9/5/2011") hence its reference in the CALCULATE() filter argument.

Where I am running into the issue is when a particular system has no data for the "DD 9/5/2011" schedule data set. In this case I would like the CALCULATE() filter argument to refer to the "DD 10/3/2011" schedule (the next earliest one). I've tried an IF() as part of the filter argument, and many-a-different approach with no success. Version 2 of the measure is just one such attempt.

In the end, I would like to have the measure
a) return the Finish date difference to the "DD 9/5/2011" date, and if this value is blank for a given system,
b) then to use the "DD 10/3/2011" value in its stead.
c) Reflect blanks if there are no values to be used for comparison

Thank you in advance for considering the issue and for any advice / lessons you can offer!

Cheers,

IG

 

[hrvat2]

Anyone have any ideas?

 

[Matt Allington]

I'm glad you have got the bug. You are on the journey to great things. There is a lot to learn, and you are doing some reasonably advanced stuff here - which is good. But from what I can see you don't have a good understanding of the basics of how Power Pivot works. As I read what you say you are trying to do, and try to read your formulas etc, it is very difficult to work it all out. It may be possible to do what you want this way, but I am almost positive it is not the best way to do it.

So as a suggestion, how about taking a step back and describe what you are trying to do. If possible post a sample workbook.

Some initial observations from me
1. your data shape looks good
2. you don't seem to be using date columns (unless DD 9/5/2011) is some strange date format that I have never seen before. This looks like a text field to me. I suggest you convert your date columns to dates
3. you will need a calendar table. Read about them here Power Pivot Calendar Tables -
4. When you write your measures, do it from within Excel, not the Power Pivot window. Set up a pivot table that is shaped the way you want to use the measure and then write the measure. That way you get to see the answer straight away and check if it is correct. I don't understand what the IF(HASONEVALUE portion of your measures are, but I doubt you need them.

 

[hrvat2]

Hi Matt. Thanks for your input and for sharing the know-how. I've been reading through quite a bit of your stuff and it's all been a great learning aid, so kudos and a big thank you for that!

What I am essentially doing is assessing the viability of having power BI as my main tool for analysis of outputs of various Project management related processes (and related reporting.) In this particular instance, I had extracted certain "schedule" data sets from ".xer" type files (are from Oracle Primavera P6 Project Management software used for planning, scheduling and controlling of programs/projects, link --> "https://www.oracle.com/applications/primavera/products/project-management.html"). A schedule file is essentially a collections of tasks, each representing some portion of project scope, which -when linked with logical relationships- yield a network over which calculations can be performed to determine desired sequencing of those tasks and their temporal order. Each "DD _____" designation is just a reference to a schedule file (essentially it's "data date" or the date it was statused) so your hunch of "DD 9/5/2011" being a text string is spot-on and in this case simply represents a particular project schedule's status date. Each such schedule file contains "start" and "finish" date-type information for various tasks contained therein.

So, if you refer to the "Draft report" link/screenshot in my initial post, the left-most column contains two types of information; 1) a "system" label (e.g. "6-73C03-10") which in this case is a reference to a particular process piping installation scope on a large refinery project, and 2) for each such system, schedule file label e.g. "DD 11/7/2011", "DD 12/5/2011" etc. Based on this "row" arrangement, I had created -for example- a measure that pulls the relevant "Finish Date" (see Column C) for that system, as based on each updating period...such that for system "6-73C03-10" the projected Finish Date as of the 11/7/2011 update was 1/11/2012 and when progress was statused on 3/5/2012, the Finish Date had slipped to 2/27/12, or a month and a half later (obviously an actual completion date here.) That "Finish Date" measure simply takes LASTDATE() of the Finish date column in the data/fact table.

I seem to be having a bit of difficulty with image posting (I saw http://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html, will try again tomorrow), but what I am essentially trying to do is create a measure [Finish Var to 9/5/11] which compares a particular system's projected finish dates over the course of schedule status updates to that of the very first projection (in this case that of the 9/5/2011 schedule). So, referring to the Report screenshot again, column D, one can see that the projected finish for system "6-73C03-10" as of 11/7/2011 is some 121 days past the projection made on some two months earlier on 9/5/2011 (Note: the "DD 9/5/2011" Finish date is cut-off / not show in the screenshot). To achieve this I used the following:

IF(HASONEVALUE('d Schedules'[Schedule Designation]), 1*([Finish Date]-CALCULATE([Finish Date],'d Schedules'[Schedule Designation]="DD 9/5/2011")), BLANK())

where [Finish Date] = LASTDATE('ALL Sched NOC Data'[Finish])

1st part: IF(HASONEVALUE('d Schedules'[Schedule Designation]) - simply makes sure the measure calc. data is displayed on a schedule update basis (e.g. "DD 9/5/2011" etc., and not for the "system"), is purely for report aesthetics.

2nd part: 1*([Finish Date]-CALCULATE([Finish Date],'d Schedules'[Schedule Designation]="DD 9/5/2011" - says to take the [Finish Date] -which again is LASTDATE() for the Finish date column of the entire fact table, and subtract from it the [Finish Date] calculated in the context of the earliest update schedule "DD 9/5/2011". Since both arguments are returning a date value I placed the "1*" portion to have it return a whole number difference as my variance.

With the above said, finally, where I encounter an issue is in those instances where the 9/5/2011 update schedule did not reflect any particular-system scope (e.g. see draft report screenshot, system "6-72C75-10", scope for which is first reflected in teh DD 10/3/2011 update schedule). in these instances the measure returns a value subtracted from zero (0) since the "DD 9/5/2011" data is blank/non-existent. So to address the issue, I was simply trying to wrap the CALCULATE() filter argument portion in a conditional statement to tell the CALCULATE() to consider the [Finish Date] measure in the context of the next earliest update schedule, "DD 10/3/2011".

Unfortunately, the following does not work:

IF('d Schedules'[Schedule Designation]="DD 9/5/2011", CALCULATE([Finish Date],'d Schedules'[Schedule Designation]="DD 9/5/2011", CALCULATE([Finish Date],'d Schedules'[Schedule Designation]="DD 10/3/2011")

nor does

IF('d Schedules'[Schedule Designation]<>"DD 9/5/2011", BLANK(), CALCULATE([Finish Date],'d Schedules'[Schedule Designation]="DD 10/3/2011")

nor do many other variants I've tried.

Apologies for the rather lengthy reply, just wanted to make sure I provide as much detail for your consideration as possible.
Let me know your thoughts and thanks again!

Cheers,

Igor

 

[Matt Allington]

wow, good explanation. Thanks. Here are some comments

1st part: fine
2nd part:
* (multiply by 1). You shouldn't need this. Just change the number format of the measure to be Whole Number and that should sort it.
* OK, so you are hard coding the first designated schedule, right? OK, perhaps this could be a slicer for user input, but if hard coded is what you need, then np. This will take the lastdate when applying a filter on "DD 9/5/2001"

So I think you want to use this logic.

if there is a finish date against the line item (assuming a single value)
then
if there is a finish date for DD 9/5/2011
then
subtract finish date for DD 9/5/2011 from the finish date of the item
else (no finish date for DD 9/5/2011)
subtract finish date for DD 10/3/2011 from the finish date of the item
else
blank()

Is this correct?