Forum Discussion

jcawley's avatar
jcawley
Icon for Helper III rankHelper III
6 years ago

Max Date Prior to a Filtered Range?

Hello,

 

I'm trying to see how many customers have came in during the last month (or any filtered range really) that have a completed appointment, who have purchased something previously from us, and who have not been into our office in the last 365 days (relative to any filtered date range).

 

I have two tables. One table (1) shows all customers who have purchased an item, and another table (2) which shows all the dates in which a customer has come to the store.

Table 1

ID
12345
23456

 

Table 2

IDTypeDateCompleted
12345CheckUp06/01/2014Yes
12345CheckUp06/28/2020Yes
23456CheckUp06/01/2014Yes
23457CheckUp06/01/2020No


Any help would be greatly apprecaited!

Need Help

5 Replies

  • AllisonKennedy's avatar
    AllisonKennedy
    Icon for Community Champion rankCommunity Champion
    6 years ago

    You could approach this a few ways. One option is to pull the info you need into the Table2 as Calculated COLUMNS: 

     

    Previous Visit Date = MAXX(FILTER(Table2, Table2[ID]=EARLIER(Table2[ID]) &&Table2[Date]<EARLIER(Table2[Date])),Table2[Date])
     
    Purchased = IF(COUNTROWS(RELATEDTABLE(Table1))>0,"Yes","No")
     
    Then create a MEASURE to determine if they are your key client or not: 
    KeyClient = IF(DATEDIFF(SELECTEDVALUE(Table2[Previous Visit Date]),MIN(DimDate[Date]),DAY)>365&&SELECTEDVALUE(Table2[Completed])="Yes" && SELECTEDVALUE(Table2[Purchased])="Yes","Yes", "No")
     
     
    • jcawley's avatar
      jcawley
      Icon for Helper III rankHelper III
      6 years ago

      Hey Allison,

      Thanks for the help! Is there a way I can get this measure to count? I think this may be the solution but when I put it in any visual format on the report, I just get a "No" as opposed to a count.

      • AllisonKennedy's avatar
        AllisonKennedy
        Icon for Community Champion rankCommunity Champion
        6 years ago
        Try reordering it to use COUNTROWS and a FILTER:

        KeyClientCount = COUNTROWS(FILTER(Table2, DATEDIFF(Table2[Previous Visit Date],MIN(DimDate[Date]),DAY)>365&&Table2[Completed]="Yes" && Table2[Purchased]="Yes"))
  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    jcawley , for prior month you can use time intelligence

    example

    last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))

     

    You can last 360 days like

    Rolling 360 day = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX(Sales[Sales Date]),-360,Day))

     

    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
    https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
    https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
    https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

    See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


    Appreciate your Kudos.

     

     

  • dax's avatar
    dax
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi jcawley 

    If possible could you please inform me more detailed information(such as your expected output and your sample data )? Then I will help you more correctly.

    Please do mask sensitive data before uploading.

    Thanks for your understanding and support.
    Best Regards,
    Zoe Zhi

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.