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

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 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
 
2 ACCEPTED SOLUTIONS
Mariusz
Community Champion
Community Champion

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

 

View solution in original post

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
 
 

 

View solution in original post

5 REPLIES 5
v-lionel-msft
Community Support
Community Support

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

r2.PNG

 

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
Super User
Super User

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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
Mariusz
Community Champion
Community Champion

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

 

View solution in original post

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
 
 

 

View solution in original post

Mariusz
Community Champion
Community Champion

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

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

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