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

[Rogerh]

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 Users table to see if the User ID came up in the Orders table. If so they were marked as Ordered, otherwise Not Ordered.

Has Ordered = IF ( 'Platform - Users'[user_id] IN DISTINCT ( 'Platform - Orders'[user_id] ), "Ordered" , "Not Ordered" )

Now i want to create another column but I only want to look at the Orders placed in the last 12 months (Column: Has orders in the last 12 months). I tried the above with Calculate and filters but can not get it to work. 

Can someone please advise the best way of doing this? Thanks

[Mariusz · Accepted solution]

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

[v-lionel-msft]

Hi @Rogerh ,

Try to create three measures.

Measure = 
CALCULATE(
    SUM('Platform - Orders'[income]),
    FILTER(
        'Platform - Orders',
        'Platform - Orders'[user_id] = RELATED('Platform - Users'[user_id])
        
    )
)

Measure 2 = 
IF(
    [Measure] = BLANK(),
    "Not Ordered",
    "Ordered"
)

Measure 3 = 
CALCULATE(
    [Measure],
    FILTER(
        'Platform - Orders',
        DATEADD( 'Platform - Orders'[created_at], -12, MONTH )
    )
)

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Rogerh , this formula can give you last 12 month of order as a measure

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH)) 
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX(Sales[Sales Date]),-12,MONTH))  

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[Mariusz · Accepted solution]

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 · Accepted solution]

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]

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