Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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" )
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:
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.
@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.
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" )
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:
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |