Counts in a period of time

[Anonymous]

Dear community,

I like to count events (open overdue transactions) in a specific period of time.

The raw data table I receive looks like the example below:

Each transaction has a start, due and closed date (once the transaction is completed), if the transaction is not closed =<30 days,

the status is overdue.

Counting running (open)  overdue transactions is not the issue, but I miss something to count them for specific time period.

As example: Transaction A was overdue starting 02.02.19 and was finally completed 08.03.19, that means in total 35 days overdue in the time period.

My desired output is a chart to trend the count of open overdue transactions over time by day, week, month, e.g. Transaction A should count as overdue for week 5 to week 10 this year, it's not count as overdue in week 1 to 4 (days running < 30 days) and starting with week 11 as the transaction was closed. 

My current count is based on days open >30 days for closed and running transactions. I have created a date table with a column to allocate the count of open overdue transactions  to the corresponding date (DD.MM.YYYY). But I am not able to built the measure.

Your help is much appreciated, many thanks,

André 

[v-xicai · Accepted solution]

Hi @Anonymous ,

According to your data, you can create a calendar table named DateKey and add one column named Week to calculate the week number in one year using DAX below.

DateKey = CALENDARAUTO()

Week = WEEKNUM('DateKey'[Date])

Then, create three measures to calculate the count of open overdue transactions over time by day, week, month below.

CountPerDay = CALCULATE(COUNT('Table'[Transaction]),'Table'[Days running]>30,FILTER(ALLSELECTED('Table'),'Table'[Due Date]<=MAX('DateKey'[Date])&&OR('Table'[Closed]>=MAX('DateKey'[Date]),'Table'[Closed]=blank())))

CountPerWeek = CALCULATE(COUNT('Table'[Transaction]),'Table'[Days running]>30,FILTER(ALLSELECTED('Table'),WEEKNUM('Table'[Due Date])<=WEEKNUM(MAX('DateKey'[Date]))&&OR(WEEKNUM('Table'[Closed])>=WEEKNUM(MAX('DateKey'[Date])),WEEKNUM('Table'[Closed])=blank())))

CountPerMonth = CALCULATE(COUNT('Table'[Transaction]),'Table'[Days running]>30,FILTER(ALLSELECTED('Table'),MONTH('Table'[Due Date])<=MONTH(MAX('DateKey'[Date]))&&OR(MONTH('Table'[Closed])>=MONTH(MAX('DateKey'[Date])),MONTH('Table'[Closed])=blank())))

Finally，Use chart visual to show the count of open overdue transactions over time by day, week, month below.

Picture 1. Show the count of overdue transactions over time by day.

Picture 2. Show the count of overdue transactions over time by week.

Picture 3. Show the count of overdue transactions over time by month .

If you have any other issue, please feel free to ask.

Best Regards,

Amy