Forum Discussion
Calculate Merchant inactivity based on transaction
Hi ,
Could someone help me in this use case how to write correct calculation measures??
Goal : Calculate Inactivity Days : Compare previous day to last day where transactions were performed
Filters for target table are set up as
| Date Filter | |
| from : 20.05.2020 | To: 21.05.2020 |
Detail table is :
| Detail data | |||
| tran No | detail_date | TID | Name |
| 1 | 01.05.2020 | Z00004 | Name 1 |
| 2 | 01.05.2020 | Z00004 | Name 1 |
| 3 | 17.05.2020 | Z00005 | Name 2 |
| 4 | 20.05.2020 | Z00005 | Name 2 |
| 5 | 13.05.2020 | Z00006 | Name 3 |
| 6 | 21.05.2020 | Z00006 | Name 3 |
Target table with gouping and calculation is :
| Date | TID | Merchant Name | Inactivity Days | inactivity days calculation description |
| 20.05.2020 | Z00004 | Name 1 | 19 | number of days from 20.05.2020 - number of days from last transaction 01.05.2020 |
| 21.05.2020 | Z00004 | Name 1 | 20 | number of days from 21.05.2020 - number of days from last transaction 01.05.2020 |
| 20.05.2020 | Z00005 | Name 2 | 3 | number of days from 20.05.2020 - number of days from last transaction 17.05.2020 |
| 21.05.2020 | Z00005 | Name 2 | 1 | number of days from 21.05.2020 - number of days from last transaction 20.05.2020 |
| 20.05.2020 | Z00006 | Name 3 | 7 | number of days from 20.05.2020 - number of days from last transaction 13.05.2020 |
| 21.05.2020 | Z00006 | Name 3 | 0 | number of days 21.05.2020 - number of days from last transaction 21.05.2020 |
Hi Anonymous ,
You could refer to my sample for details.
Best Regards,
Zoe ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
- amitchandak
Super User
Anonymous , with date table if this example can help
Last Day Non Continous = CALCULATE(sum('order'[Qty]),filter(all('Date'),'Date'[Date] =MAXX(FILTER(all('Date'),'Date'[Date]<max('Date'[Date])),'order'['Date'])))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. 
daxCommunity Support
Hi Anonymous ,
You could refer to my sample for details.
Best Regards,
Zoe ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
