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 d...
Need Help
Tutorial Requests
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.