Forum Discussion

bolabuga's avatar
bolabuga
Helper V
8 years ago
Solved

[ 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...
Need Help
  • bolabuga's avatar
    bolabuga
    8 years ago

    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's avatar
Phil_Seamark
Microsoft Employee
8 years ago

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's avatar
    bolabuga
    Helper V
    8 years ago

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

  • bolabuga's avatar
    bolabuga
    Helper V
    8 years ago

    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's avatar
      Phil_Seamark
      Microsoft Employee
      8 years ago

      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's avatar
        bolabuga
        Helper V
        8 years ago

        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.