[ Creating 2 calculated columns ]

[bolabuga]

Hello everyone.

I have the following scenario:

- In the first calculated column i want the date of the newest service existing in a period >= [revenuedate - 405 days] and <= [revenuedate - 40]

- I need the newest date, because in the 365 days window, i can have 0 or 1 or 2 or more ocurrences of different services for a given client

- The newest date to get is based on a hierarchy of the services, where 1 is the highest priority, as seen on the screenshot

 

- In the second calculated column i would like to get the service name related to the date returned in the first created calculated column

I will upload the pbi file to help understand better my doubts.  In this file the calculated columns are created and their names are "MAXSERVICE YEARMONTH" and "SERVICE NAME"

pbi file: https://drive.google.com/open?id=0B8Aq8DhGApJqWDZlLTdQYnFibDQ

[Phil_Seamark]

HI @bolabuga

How close is this calculated column for your first requirement?

I'm not clear what you need to do with Hierarchy

Column = 
    MAXX(
        FILTER(
            'BASE SERVICES',
            'BASE SERVICES'[CLIENT] = EARLIER('BASEDATA'[CLIENT]) &&
            'BASE SERVICES'[DATE] >= EARLIER('BASEDATA'[REVENUEDATE]) - 405 &&
            'BASE SERVICES'[DATE] < EARLIER('BASEDATA'[REVENUEDATE]) - 40  
            
            ),
            
            'BASE SERVICES'[DATE]) 

[bolabuga · Accepted solution]

@Phil_Seamark

It seens to work this way in the test data base that i shared with you. (I will need to test this on the actual data base to be 100%):

Column = 
VAR MINH = 
MINX(
    FILTER(
        'BASE SERVICES';
        'BASE SERVICES'[CLIENT] = EARLIER(BASEDATA[CLIENT])
        && 'BASE SERVICES'[DATE] >= EARLIER('BASEDATA'[REVENUEDATE]) - 405 
        && 'BASE SERVICES'[DATE] < EARLIER('BASEDATA'[REVENUEDATE]) - 40);
    'BASE SERVICES'[HIERARCHY])
RETURN          
    MAXX(
        FILTER(
            'BASE SERVICES';
            'BASE SERVICES'[CLIENT] = EARLIER('BASEDATA'[CLIENT]) &&
            'BASE SERVICES'[DATE] >= EARLIER('BASEDATA'[REVENUEDATE]) - 405 &&
            'BASE SERVICES'[DATE] < EARLIER('BASEDATA'[REVENUEDATE]) - 40
            && 'BASE SERVICES'[HIERARCHY] = MINH);
        DATE(YEAR('BASE SERVICES'[DATE]);MONTH('BASE SERVICES'[DATE]);1))

Question: Why it doesnt work this way??

Column = 
MAXX(
        FILTER(
            'BASE SERVICES';
            'BASE SERVICES'[CLIENT] = EARLIER('BASEDATA'[CLIENT]) &&
            'BASE SERVICES'[DATE] >= EARLIER('BASEDATA'[REVENUEDATE]) - 405 &&
            'BASE SERVICES'[DATE] < EARLIER('BASEDATA'[REVENUEDATE]) - 40
            && 'BASE SERVICES'[HIERARCHY] = MIN('BASE SERVICES'[HIERARCHY]);
        DATE(YEAR('BASE SERVICES'[DATE]);MONTH('BASE SERVICES'[DATE]);1))

[Phil_Seamark]

Hi @bolabuga

In your second example the like that goes

BASE SERVICES'[HIERARCHY] = MIN('BASE SERVICES'[HIERARCHY]);

is pretty much dropping down to a single line.  If you want to use a single statement, you'd need to nest your filter statements, but you will still end up with a similar amount of code - and no performance gain.

[bolabuga]

@Phil_Seamark

Thks Phil for the help.

Pratical Dax are certainly in my TARGET, but before that i need to finish my reading of "definitive guide to dax", which i bought recently. 

[bolabuga]

@Phil_Seamark

Thks for the reply phil.

Hierarchy is really important.

Imagine this scenario for 1 client:

My revenue date is 11/12/17 (dd/mm/yy)

I need filter service table looking for services on that client number, based on the revenuedate.

Looking into a period of 405 days before revenue date.

Lets supose i have 3 services located in the 405 days period, after the filters happen: 

- service A in 10/01/17 (dd/mm/yy)

- service B in 15/03/17 (dd/mm/yy)

- service D in 17/05/17 (dd/mm/yy)

In the revenue table i can only have 1 date from those 3 services as a viable result per client for the calculated column "MAXSERVICE YEARMONTH", and here is where i need the hierarchy, i want to follow the result of the most important service based in the hierarchy table, and not always the most important service is the newest one. In the scenario above, even if i have newer services (B,D), i want as result the date of the "A" service.

In fewer words, i need to filter service table in such a way, where i alwas get the "maximum date" of the "minimum Hierarchy" available on the context (client number and period of days).