Forum Discussion

Rogerh's avatar
Rogerh
Icon for Helper II rankHelper II
6 years ago
Solved

IF function - looking up another table and filter by the last 12 months

Good morning Community   I am relativly new to DAX and need a hand completing the below please.    I have two tables : Platform - Orders and Platform - Users. I created a custom column on the Use...
Need Help
  • Mariusz's avatar
    Mariusz
    6 years ago

    Hi Rogerh 

     

    Try something like:

    Has Ordered in last 12 months = 
VAR __Salse12Months =
    CALCULATE(
        [Sales],
        DATESINPERIOD( 'Calendar'[Date], TODAY(), -12, MONTH )
    ) 
RETURN 
    IF( ISBLANK( __Salse12Months ), "Not Ordered", "Ordered" )

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    LinkedIn

     

  • Rogerh's avatar
    Rogerh
    6 years ago

    Hi Mariusz 

     

    Thank you for your reply,

    Following the your reply I created a 'Sales' Measure for the full Orders Table and used my Date Table. However all of them have come up as Not Ordered.

     

    Can you think of any edits to your code to make it work? Or is there something i could have missed? Thanks

     

    I understood where you were going with your code though and you gave me an idea. So I created a column that summed all of the orders in the last 12 months against the user:

     

    Sum.Income in the last 12 months =
    CALCULATE(SUM('Platform - Orders'[income]),FILTER('Platform - Orders', 'Platform - Orders'[user_id] = 'Platform - Users'[user_id]), 'Platform - Orders'[created_at] > TODAY() - 365)
     
    Then created a simple IF
     
    Has Ordered in last 12 months = if('Platform - Users'[Sum.Income in the last 12 months] = BLANK(), "Not Ordered", "Ordered")
     
    And this has work 🙂
     
    Since I am new to this, do you think this is a good workaround?
    Thanks
     
     

     

Mariusz's avatar
Mariusz
Icon for Community Champion rankCommunity Champion
6 years ago

Hi Rogerh 

 

Try something like:

Has Ordered in last 12 months = 
VAR __Salse12Months =
    CALCULATE(
        [Sales],
        DATESINPERIOD( 'Calendar'[Date], TODAY(), -12, MONTH )
    ) 
RETURN 
    IF( ISBLANK( __Salse12Months ), "Not Ordered", "Ordered" )

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

 

Rogerh's avatar
Rogerh
Icon for Helper II rankHelper II
6 years ago

Hi Mariusz 

 

Thank you for your reply,

Following the your reply I created a 'Sales' Measure for the full Orders Table and used my Date Table. However all of them have come up as Not Ordered.

 

Can you think of any edits to your code to make it work? Or is there something i could have missed? Thanks

 

I understood where you were going with your code though and you gave me an idea. So I created a column that summed all of the orders in the last 12 months against the user:

 

Sum.Income in the last 12 months =
CALCULATE(SUM('Platform - Orders'[income]),FILTER('Platform - Orders', 'Platform - Orders'[user_id] = 'Platform - Users'[user_id]), 'Platform - Orders'[created_at] > TODAY() - 365)
 
Then created a simple IF
 
Has Ordered in last 12 months = if('Platform - Users'[Sum.Income in the last 12 months] = BLANK(), "Not Ordered", "Ordered")
 
And this has work 🙂
 
Since I am new to this, do you think this is a good workaround?
Thanks
 
 

 

  • Mariusz's avatar
    Mariusz
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi Rogerh 

     

    Yeh, sure your solution looks good and most importantly it worked for you!

    My solution should work as well If you have a relationship between 'Platform - Orders'[user_id] and 'Platform - Users'[user_id]  columns.

     

    Nevermind, glad you've got it sorted.


    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    LinkedIn