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 ...
Need Help
Tips and Tricks
Tutorial Requests
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.