Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hello dear colleagues,
I'm trying to create a calculated column in the following context:
I have an "Event Attendees" table in which I have a row for each attendee of promotional events. I need to know if within 30 days of the date of the event an order has taken place.
Regarding my model, I have my "Event Attendees" table, my "Sales" table but they have no relationship between them. They are both linked to my "Customer" and "Calendar" dimensions.
I've already created a new column that gives us the date at Days + 30, as you see below:
Event Name | Event Date | Days +30 | Attendee | Sales D+30 ? |
Event 1 | 01/01/2022 | 31/01/2022 | Customer A | |
Event 1 | 01/01/2022 | 31/01/2022 | Customer B | |
Event 1 | 01/01/2022 | 31/01/2022 | Customer C |
I have already tried the following formula:
But it is not working.
Do you have any idea how I can solve this? If my formula is correct, does the problem come from my model?
Any help or new suggestion will be welcome 🙂
Thank you in advance for your precious help!
Pauline
Solved! Go to Solution.
Hi @DIACHROMA,
You may try this Measure.
Sales D+30? =
CALCULATE (
COUNT ( Sales[OrderDate] ), //Count Order in the Range
TREATAS ( VALUES ( 'Events Attendees'[Attendee] ), Sales[Customer] ),
FILTER (
'Calendar',
'Calendar'[Dates] >= MINX ( 'Events Attendees', 'Events Attendees'[EVENT DATE] )
&& 'Calendar'[Dates] <= MINX ( 'Events Attendees', 'Events Attendees'[DAYS +30] )
)
)
Then, the result will look like this.
Also, attached the pbix file as reference.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!
Best Regards,
Community Support Team _ Caiyun
@DIACHROMA it is pretty straightforward since you have the model already in place, just add the following measure:
Sales D+30 New? =
VAR __numberOfDays = 30
VAR __eventStartDate = MIN ( 'Events Attendees'[Event Date] )
VAR __eventEndDate = __eventStartDate + __numberOfDays
RETURN
CALCULATE (
COUNTROWS ( Sales ),
DATESBETWEEN (
'Calendar'[Dates],
__eventStartDate,
__eventEndDate
)
)
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Hi @DIACHROMA,
You may try this Measure.
Sales D+30? =
CALCULATE (
COUNT ( Sales[OrderDate] ), //Count Order in the Range
TREATAS ( VALUES ( 'Events Attendees'[Attendee] ), Sales[Customer] ),
FILTER (
'Calendar',
'Calendar'[Dates] >= MINX ( 'Events Attendees', 'Events Attendees'[EVENT DATE] )
&& 'Calendar'[Dates] <= MINX ( 'Events Attendees', 'Events Attendees'[DAYS +30] )
)
)
Then, the result will look like this.
Also, attached the pbix file as reference.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!
Best Regards,
Community Support Team _ Caiyun
Hi maybe something like that
--- No relationships between 'Calendar' & 'Events Attendees' tables , just a dynamic filetring
--- But relationships between 'Calendar' & 'Sales' tables
Sales D+30 =
Var CurrentDate
= MAX('Calendar'[Dates]) RETURN
CALCULATE (
[Total Sales] ,
FILTER ( 'Events Attendees' , 'Events Attendees'[EVENT DATE] >= CurrentDate && 'Events Attendees'[DAYS +30] <=CurrentDate
)
)
Table visual ==>
Event name | Calendar'[Dates] | Sales D+30
Hello,
Is an attendee not supposed to be the 'customer' in the Sales table if he places an order ?
Have you tried using the AND operator instead of two 'FILTER' operations ?
Like this :
Just Make a relationship between 2 tables.
And one important thing to remember, your sale table is your main table.
IF your event are all 30 days,
create a new table.
For fun only, a showcase of powerful Excel worksheet formula,
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Hello,
The key for me is to build an Event Table thanks to your attendee table.
Then, you can create a calculated Column to do the job.
https://drive.google.com/file/d/19U2E8QPUBG9x_XzExM2lYHcpB4y-vPIf/view?usp=sharing
Regards
Hi @CNENFRNL thank you very much for this proposal !
However, in my Sales table I don't have the "Relevant Event" column.
I have events on one side, sales on the other, but no way to link an event to the sales generated behind it. It is for this reason that we look at 30 days following an event to find out if over this period the participant has generated sales.
User | Count |
---|---|
21 | |
20 | |
14 | |
10 | |
8 |
User | Count |
---|---|
30 | |
28 | |
13 | |
12 | |
11 |