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

     

     

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.