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.
Hi everyone,
i'm working on a solution to enable my users for the following:
Here is my test data setup:
1. Activities:
2. Customers:
3. Questions:
Relation:
Examples:
1. The answer of Q1 (questions.Answer) for customer1 in June.
Expected result would be =1
2. The answer of Q1 for customer 2 in June:
Expected result = null (since last activity in June for customer 2 was ID = 7 at 25.06.)
3. The answer of Q1 for customer 4 in July:
Expected result = 10
4. Sum of Q1 for all customers in July:
Expected result = 11 (customer 1 = 1 + customer 4 = 10 )
My Problem is that i only want to count the most recent activity (and it's related question data) for each customer in the given timeframe specified by the slicer.
Does anyone have an idea how to do this?
best regards and looking forward to any help,
Julian
for each customer.
Didn't look at your PBIX file but you should be able to use MAX to grab the latest date from your slicer. Then you can just do a MAXX on an ALLSELECTED of your table that is FILTER'ed to dates less than your MAX. Then it is simply a FILTER on an ALLSELECTED for entries that match that given date and then an "X" function (SUMX, MAXX, AVERAGEX, etc.) for whatever you are trying to caculate. If I have time, I'll take a look at the PBIX file.
I have now created the following measure:
PrevSaleDate =
var maxdate = MAX ( 'Activities'[Date] )
Var customerMax= MAXX (
FILTER ( ALLSELECTED ( 'Activities' ); 'Activities'[Date] <= maxdate );
[Date]
)
RETURN
CALCULATE(SUM(Questions[Answer]);
FILTER ( 'Questions'; customerMax = Questions[Date] )
)
The intermediate step
customerMax
gives me the correct dynamic max date per customer.
Expected result is :
Customer 1 = 1
Customer 2 = 2
Customer 3 = 4
Customer 4 = 1
It is working so far, i will continue on.
Hi @Greg_Deckler,
thank you for the hints but i couldn't come up with a working formula yet.
I would be very grateful if you could provide an example for me.
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 |
---|---|
96 | |
93 | |
82 | |
70 | |
64 |
User | Count |
---|---|
116 | |
106 | |
94 | |
79 | |
72 |