Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Filter historic data dynamically by time periods

I have a table with historic data, some of the values were updated which means that if we look at it on a table there will be duplicate data. What I would like to do is that given a date range, the data displayed will be the latest version according to the time period.

 

Pd: date format DD/MM/YYYY

 

So for example, we have Customer 1, the payments recorded are all sequential until we get to payment 3. Here we have a duplicate. Both on March 5th but with a log date where the payment was created on February 1st but then updated on March 6th where the status was changed to paid as it was charged on March 5th. (The same situation for Customer 2 with payment 1).

 

 

So here when I filter the date of the payment, I get both results instead of just the updated data (based on the log date).

 

If I could filter this by the payment date, I would need it to only show the updated data based on the most recent log date for the specified dynamic filter below (year/month):

 

 

I used this relation for the date filter. 

 

Now, the thing is, I know this can be done with columns but this will be static. I need the results to be dynamic so that if the payment date filter is adjusted, then the results displayed will always be the most recent based on the log date. 

 

I have tried many methods but none seem to work, I would really appreciate any help.

Need Help
Tutorial Requests

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    7 years ago

    Create a filtering measure similar to something like this:

     

    __Show = 
VAR __table = SUMMARIZE(ALL(Table5),[Level],"__Min",MIN([Price]))
VAR __level = MAX([Level])
VAR __price = MAX([Price])
RETURN
IF(__price = MAXX(FILTER(__table,[Level] = __level),[__Min]),1,0)

    For a different problem, but same concept. You would use something like MAX([LogDate]) instead of MIN([Price]).

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      I implemented the measure but I have a inconvenient:

       

      __Show = 
VAR __table = SUMMARIZE(ALL(CustomerPayments);CustomerPayments[PaymentDay];"__Max";MAX(CustomerPayments[LogDate]))
VAR __level = MAX(CustomerPayments[PaymentDay])
VAR __logdate = MAX(CustomerPayments[LogDate])
RETURN
IF(__logdate = MAXX(FILTER(__table;CustomerPayments[PaymentDay] = __level);[__Max]);1;0)

      Keeping the data set the filter works.

       

       

      But if in the data set registers new customers who pay in February 2019. The measure filters those with the highest LogDate. In this case excluding "Customer 4" because the maximum LogDate is "6/3/2019" and the LogDate of "Customer 4" is "1/2/2019". This customer must be included since he pays in the mentioned period.

       

       

      I tried several ways, but I don't know how to perform the IF condition with a list.