Metric Calculation Adding Holiday & Weekend Metrics to Last business day Counts

[Tenairen]

Good afternoon!

I have been playing with this for a bit, and am not having any luck so maybe i am overlooking something!

I have two tables configured, one is a date table (TableOfDates):

Date	DayOfWeek	Month	Year	FiscalQuarter	FiscalYear	BusinessDay
Dec 29, 2023	Friday	December	2023	Q3	2023/24	Yes
Dec 30, 2023	Saturday	December	2023	Q3	2023/24	No
Dec 31, 2023	Sunday	December	2023	Q3	2023/24	No
Jan 1, 2024	Monday	January	2024	Q4	2023/24	No
Jan 2, 2024	Tuesday	January	2024	Q4	2023/24	Yes
Jan 3, 2024	Wednesday	January	2024	Q4	2023/24	Yes
Jan 4, 2024	Thursday	January	2024	Q4	2023/24	Yes
Jan 5, 2024	Friday	January	2024	Q4	2023/24	Yes
Jan 6, 2024	Saturday	January	2024	Q4	2023/24	No
Jan 7, 2024	Sunday	January	2024	Q4	2023/24	No
Jan 8, 2024	Monday	January	2024	Q4	2023/24	Yes
Jan 9, 2024	Tuesday	January	2024	Q4	2023/24	Yes

The other Defines our incoming Calls by IDs (Calls)

CallID	Date	Contact Type	Issue ID	Sub-Issue ID	Status
014562	Dec 29, 2023	New Call	10	6	In Progress
014563	Dec 29, 2023	Update	5	2	New
014564	Dec 29, 2023	Update	4	2	Pending Assessment
014565	Dec 30, 2023	New Call	10	2	In Progress
014566	Dec 30, 2023	New Call	8	2	Complete
014567	Dec 31, 2023	Update	3	1	In Progress
014568	Dec 31, 2023	New Call	1	1	Pending Assessment
014569	Jan 1, 2024	New Call	1	2	New
014570	Jan 1, 2024	Update	3	3	Pending Assessment
014571	Jan 2, 2024	New Call	10	3	In Progress
014572	Jan 2, 2024	New Call	10	3	Complete
014573	Jan 3, 2024	New Call	6	4	Complete
014574	Jan 3, 2024	Update	6	5	Complete
014575	Jan 3, 2024	New Call	8	7	In Progress
014576	Jan 4, 2024	New Call	9	5	New
014577	Jan 4, 2024	Update	5	4	Complete
014578	Jan 4, 2024	New Call	10	3	New
014579	Jan 5, 2024	Update	2	2	Complete
014580	Jan 6, 2024	New Call	1	1	New
014581	Jan 8, 2024	Update	1	5	In Progress

What i need to do is create a count of volume on a given date however, whoever created the metric originally has included any volumn on a weekend date or holiday date in the last business day -- So above for example Dec 30, 31 & Jan 1 counts should be included within Dec 29.... 

I have been able to create a measure which workes well to include the volumn on a weekend day in the leading Friday total: 

zVolCounter(RemoveSat/Sun) =

VAR _VolCounterNOSatSun = Count('Calls'[ID]) RETURN

IF( 

    HASONEVALUE('TableOfDates'[Date]),

    SUMX(

        Values('TableOfDates'[Date]),

        IF(

            Weekday('TableOfDates'[Date]) = 6, 

            _VolCounterNOSatSun 

            +CALCULATE( 

                COUNT('Calls'[ID]),

                NEXTDAY('TableOfDates'[Date])

            )

            +CALCULATE( 

                COUNT('Calls'[ID]),

                NEXTDAY(NEXTDAY('TableOfDates'[Date]))

            ),

            IF(WEEKDAY('TableOfDates'[Date]) IN {1,7},Blank(),_VolCounterNOSatSun) 

        )

    ), _VolCounterNOSatSun

)

So far I have not been able to figure out how to do this with the Business day field to add my holidays count to the previous business day count.  Any help at all would be highly appriciated (I am not using slicers or filters... I just need a overall count daily)

[ThxAlot · Accepted solution]

Easy enough, create a calculated column for the start date of each bucket,

of coz, the logic can be implemented on the fly by a measure,

Bucket_Start = 
VAR __dt =
    MAX( DATES[Date] )
RETURN
    CALCULATE(
        MAX( DATES[Date] ),
        DATES[Date] <= __dt,
        DATES[BusinessDay] = "Yes"
    )

[Tenairan]

Sorry guys, i ended up in the sick and away for a bit,  this solution solves the issue of getting into the date buckets vs by date. I am seeing some duplication in the dates when quantifying the number of calls... the assumption i am having is because i am using the date bucket vs the date ?