Forum Discussion

JulianS's avatar
JulianS
Advocate II
8 years ago

time intelligence get max date with slicer time

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.

Need Help
Tips and Tricks
Tutorial Requests

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    8 years ago

    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's avatar
      JulianS
      Advocate II
      8 years ago

      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.

    • JulianS's avatar
      JulianS
      Advocate II
      8 years ago

      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.