Applying a filter to IF function (DAX related question)

[reinholz]

Hi,

I'm fairly new to Power BI and the use of DAX. That's why I'm not sure if I'm doing something fundamentally wrong or if I just didn't find the right solution yet. 

Here is my problem:

I have a dataset listing all the changes of the stage of sales opportunities.

It has columns for "Opportunity Name", "Field/Event", "Old Value", "New Value", "Edit Date" and "Amount". It looks like this:

What I now want to do is to add two columns. One should show the Month & Year of the old stage and one should show the Month & Year of the new stage. This is needed to see if the change in stage happend within one month or in the next month.

It should then look like this:

In this example the opportunity "Company A - 9 Months service" went through two stages in 06/16 and then changed to stage "closed" in 07/16. Those two columns are exactly what I need, but I just can't figure out a way how to get there. The "New Stage Month" column obviously is easy to do, but the "Old Stage Month" so far isn't.

This is how far I got:

1. I added a calculated column that formats the "edit date" to a number that is "YYYY0MM". (eg. 201606)

2. I used the formatted "edit date" number to apply a RANKX function filtered by "Opportunity Name".

That way I was able to get a rank for each row, on the opportunity level. In the example above that would mean that the first two entries for Company A have the rank 1 and the third entry has the rank 2 (since the first two are in 06/16 and the third is in 07/16). 

3. I added a index for each entry, also on the opportunity level. (that means every entry for an opportunity has a unique number) 

In the example above that would mean that the first entry for Company A is "1", the second "2", the third "3", ...

This is needed to be able to reference to the prior row/entry (of the respecitve opportunity), and therefore to be able to compare if the edit dates lie within the same or in different months. 

The table now looks something like this:

And from here on I'm stuck. My idea was to use a nested IF function that would look like this: 

Old Stage Month = 
IF(
[index] = 1; FORMAT([Edit Date];"MM.YY");
IF(
[date_rank] > LOOKUPVALUE([date_rank]; [index]; [index]-1); 
FORMAT(LOOKUPVALUE([Edit Date]; [index]; [index]-1);"MM.YY");
FORMAT([Edit Date]; "MM.YY")
))

The first IF function basically sets the "Old Stage Month" field to the formatted Edit Date, if the rank is 1 (which means there are no entries before that one). If the rank isn't 1 it checks wether the "date_rank" is greater than that of the prior entry. If that's the case it uses the "Edit Date" of the prior entry. So far so good.

If I use this formula in a table that is filtered to one specific "Opportunity Name", it works perfectely fine.

What I didn't figure out is how to add a filter to that IF formula, so that it also works in my whole dataset. The filter would need to filter the data on the "Opportunity Name" level and then apply the IF statement for each "Opportunity Name". 

Is there a way to do that? 

Maybe my whole approach is way too complex, I don't know. But I'm looking forward to any help I can get, since I'm a little desperate right now. 

Thanks!

Best,

Sebastian

(Sorry for the endless post. I hope it's not too confusing. I just tried to describe the problem as accurate as possible.)

[Vvelarde · Accepted solution]

hi @reinholz

Try with this calculated column:

Old Stage Month =
FORMAT (
    CALCULATE (
        MAX ( 'Sales Opportunities'[Edit Date] );
        FILTER (
            'Sales Opportunities';
            'Sales Opportunities'[Edit Date] < EARLIER ( 'Sales Opportunities'[Edit Date] )
                && 'Sales Opportunities'[Opportunity Name] = EARLIER ( 'Sales Opportunities'[Opportunity Name] )
        )
    );
    "MM/YY"
)

[reinholz]

Wow, that is way easier than my approach!

It worked!

Thanks a lot!!