Calculate Past 10 Days Activity Based on Selected Date Excluding Holidays and Weekends

[tmfitz]

Hello all,

I’m creating a financial activity report for one of our funds which we obtain detailed account activity information on a daily basis. One of the report displays needs to show activity for the 10 days prior to the date selected. Weekends and holidays should be included in the count. I’ve struggled through Googling and now that I’m a member, I’ve search through the forum but didn’t find exactly what I needed or wasn’t able to translate into my use case.

I will attach my PBIX file but here’s my scenario:

1. I have a DIM_Calendar table which does contains a column called Day Type field which categorizes a date as Weekday, Holiday or Weekend.
2. I also created a Selected Date table to keep the selecting of the date separate. I saw this in one of the videos I watched and it seems to work well. There is a relationship from this table to the DIM_Calendar table.
3. The FACT_Data contains all my transaction data loaded on a daily basis.

I created this one measure to start to try to get the last 10 days from select date:

10 Day Net Activity Walkforward =
VAR SelectedDate = CALCULATE(MAX(‘Selected Date’[Date]),ALL(DIM_Calendar[date]))
RETURN
IF(AND(MAX(DIM_Calendar[Date])>SelectedDate - 10, MAX(DIM_Calendar[Date])<=SelectedDate),
[TTL Net Activity],
BLANK())

It’s probably clear to most of you that this will not account for holidays and weekends.

I created another measure just to see if I could get to the last 10 days from the Selected Date:

Last 10 Work Days =

var d = max(‘Selected Date’[Date])

VAR Last10WorkDays = TOPN(10,FILTER(ALL(DIM_Calendar),DIM_Calendar[Day Type]=“Weekday” && DIM_Calendar[Date]<=d),DIM_Calendar[Date],DESC)

RETURN

CONCATENATEX(Last10WorkDays,DIM_Calendar[Date],",")

From here I could see that, yes, I can get see the last 10 days which doesn’t display any holidays or weekends in the range.

I’m having a hard time combining the two or at least coming up with a way to make this work. Your help/advise would be appreciated.

Reporting Help scrubbed.pbix (2.0 MB)

[Melissa]

Hi @tmfitz,

Thanks for supplying a PBIX
I think you were so close to resolving this… Give this a go

Last 10 Work Days v2 = 
VAR d = MAX( 'Selected Date'[Date] )
VAR Last10WorkDays =
    SELECTCOLUMNS( 
        TOPN( 10, 
            FILTER( ALL(DIM_Calendar), 
                DIM_Calendar[Day Type] = "Weekday" && 
                DIM_Calendar[Date] <= d
            ),  DIM_Calendar[Date], 
            DESC 
        ),  "@Date", [Date]
    )
RETURN 

IF( MAX( DIM_Calendar[Date] ) IN Last10WorkDays,
    [TTL Net Activity]
)

[tmfitz]

Yes! That worked! thanks so much.

[jbressan]

Hi @tmfitz

If you want Add Totals you can make a small change to the measures

Last 10 Work Days v2 = 
VAR d =
    MAX ( 'Selected Date'[Date] )
VAR Last10WorkDays =
    SELECTCOLUMNS (
        TOPN (
            10,
            FILTER (
                ALL ( DIM_Calendar ),
                DIM_Calendar[Day Type] = "Weekday"
                    && DIM_Calendar[Date] <= d
            ),
            DIM_Calendar[Date], DESC
        ),
        "@Date", [Date]
    )
VAR _Result =
    IF ( MAX ( DIM_Calendar[Date] ) IN Last10WorkDays, [TTL Net Activity] )
VAR _Total =
    CALCULATE ( [TTL Net Activity], ALL ( DIM_Calendar ), Last10WorkDays )
RETURN
    IF ( ISINSCOPE ( DIM_Calendar[Date] ), _Result, _Total )

[tmfitz]

@jbressan - Thanks! I don’t need to for this particular view but I’m sure this will come in hand for another that I have to build.