Count number of transactions rolling 8 weeks - time intelligence

[Anonymous]

Thank you in advance for any help you can give me! I always struggle with time intelligence 🙂

My data is laid out in the following format:

Date submitted	Start of week	End of week
28/8/23	28/8/23	3/9/23
27/8/23	21/8/23	27/8/23

I've been trying to count using the below measure:

Measure = countrows(table)

I need to count the number of complaints for the last week (excluding the current week) then an 8 week total and a ytd total.

This formula comes up blank: 

Count of Ref No =

IF(

    ISFILTERED('hotjar'[End of Week]),

    ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),

    VAR __LAST_DATE = LASTDATE('hotjar'[End of Week].[Date])

    RETURN

        COUNTX(

            DATESBETWEEN(

                'hotjar'[End of Week].[Date],

                DATEADD(__LAST_DATE, -7, DAY),

                __LAST_DATE

            ),

            CALCULATE(COUNTA('hotjar'[Ref No]))

        )

)

I have a date table that is linked to a submitted date so the [end of week] and [start of week] both have hierachies.

I've also played around with two sets of dateadd in the above formula:

DATEADD(__LAST_DATE, -7, DAY),

DATEADD(__LAST_DATE, 0, DAY),

...but it's either coming up blank or isn't adding up correctly.

I've also tried with the weeknum but I haven't been able to work it out.

[Anonymous · Accepted solution]

Hi @AmiraBedh,

Appreciate you putting a solution online for me.

I managed to work it out in a different way (posting online for others).

Created: 

BS_last week = MAX(hotjar[Date End of Week])-7

BS_last 8 week = MAX(hotjar[Date End of Week])-63

Count of Ref No v3 =

        CALCULATE(COUNTROWS(hotjar),DATESBETWEEN('Calendar'[Date],[BS_last 8 week],[BS_last week]))

[AmiraBedh]

I am breaking down your issue in 3 parts :

1. **Last Week Total**: Count the number of complaints for the last week (excluding the current week).
2. **8-Week Total**: Count the number of complaints for the last 8 weeks (including the last week).
3. **YTD Total**: Count the number of complaints year-to-date.

Based on the table structure you shared :

**1. Last Week Total**:

Last Week Total =
VAR LastDateInData = MAX('hotjar'[End of Week])
VAR StartOfLastWeek = LastDateInData - 7
RETURN
COUNTROWS(
FILTER(
'hotjar',
'hotjar'[End of Week] <= LastDateInData && 'hotjar'[End of Week] > StartOfLastWeek
)
)

**2. 8-Week Total**:

8 Week Total =
VAR LastDateInData = MAX('hotjar'[End of Week])
VAR StartOf8WeeksAgo = LastDateInData - 56
RETURN
COUNTROWS(
FILTER(
'hotjar',
'hotjar'[End of Week] <= LastDateInData && 'hotjar'[End of Week] > StartOf8WeeksAgo
)
)

**3. YTD Total**:

This will use your date table linked to the submitted date:

YTD Total =
VAR TodayDate = TODAY()
RETURN
COUNTROWS(
FILTER(
'hotjar',
'hotjar'[Date submitted] <= TodayDate && YEAR('hotjar'[Date submitted]) = YEAR(TodayDate)
)
)