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

[ Creating 2 calculated columns ]

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

HIERARQUIA.PNG 

 

- 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

 

 

1 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))

 

 

View solution in original post

5 REPLIES 5
Microsoft
Microsoft

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]) 

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

@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))

 

 

View solution in original post

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.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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

 

 

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

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors