Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
ID | Type | Date | Completed |
12345 | CheckUp | 06/01/2014 | Yes |
12345 | CheckUp | 06/28/2020 | Yes |
23456 | CheckUp | 06/01/2014 | Yes |
23457 | CheckUp | 06/01/2020 | No |
Any help would be greatly apprecaited!
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.
@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.
You could approach this a few ways. One option is to pull the info you need into the Table2 as Calculated COLUMNS:
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.
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |