time intelligence get max date with slicer time

[JulianS]

Hi everyone,

i'm working on a solution to enable my users for the following:

• use a between slicer for activity date 
• get latest activity data for each customer within the given time frame

• adjust the result according to the slicer 

Here is my test data setup:

1. Activities:

2. Customers:

3. Questions:

Relation:

example power bi file

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.

[Greg_Deckler]

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.

[JulianS]

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.

[JulianS]

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.