Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jcawley
Helper III
Helper III

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!

5 REPLIES 5
dax
Community Support
Community Support

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.

amitchandak
Super User
Super User

@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-Y...


Appreciate your Kudos.

 

 

AllisonKennedy
Super User
Super User

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")
 
 

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

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.

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"))

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.