Calculate Merchant inactivity based on transaction

[striker41]

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

[dax · Accepted solution]

Hi @striker41 , 

You could refer to my sample for details.

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]

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


Appreciate your Kudos.