Running Total, last 5 BILLABLE days from today, rolling

[Fro88er]

Below I have a DimCalendar dimension.  I am trying to average [PDV Combined Total] for the last 5 days of billing rolling by identifiny the last 5 billing days on the calendar table.  I have identified the non-weekends and Holidays column: [IsABillingDay], next I have identified is <= Today column: [BillableFromToday].  I can't figureout how to only grab/measure the first 5 values from [BillableFromToday]. I tried, rankx (average if<=5), indexing (average if<=5), etc.  Open to ideas, thoughts?

BillableFromToday =

IF(DimCalendar[Date]<= TODAY() && DimCalendar[IsABillingDay] = 1,

1)

IsABillingDay =

VAR BillingDay =

SWITCH(TRUE(),

DimCalendar[WeekDay#] = 1, 0,

DimCalendar[WeekDay#] = 7, 0,

1)

Var Holiday =

LOOKUPVALUE(DimCalendarHolidays[Qty], DimCalendarHolidays[Date], DimCalendar[Date])

Return

BillingDay + Holiday

[Fro88er]

This worked perfectly!  Extreamly helpful to see and learn the logic applied.

Thank you so much,
Mark

[Ashish_Mathur]

Hi,

I posted my solution at this Blog article as well - Show Balance outstanding everyday even if data for everyday is not available.

[Ashish_Mathur]

You are welcome.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.  Since today is December 2, it is counting 5 days backwards from December 2.  Also, i have solved it without using the BillableFromToday column.  I have shown the result as sum (not average) so that you can check my result easily.

Hope this helps.

[TomMartens]

Hey @Fro88er ,

I assume this will work, the idea behind this is to identify the last five billable days, store this into a variable and use this variable inside the calculate.

the average = 
var __currendate = MAX( 'DimCalendar'[Date] )
var dates = 
SELECTCOLUMNS(    
    TOPN( 
        5
        , FILTER( ALL( 'DimCalenar'  , 'DimCalendar'[Date] <= __currendate && 'DimCalendar'[IsABillingDay] = 1 ) 
        , INT( 'DimCalendar'[Date] ) 
        , DESC 
    )
    , "Date" ,  'DimCalendar'[Date]
)
return
CALCULATE(
    AVERAGE(...[PDV Combined Total]
    , dates
)

Hopefully, this provides what you are looking for.

If not, consider to create a pbix file that contains sample data, but still reflects your data model, upload the file to onedrive or dropbox and share the link.

Regards,

Tom

[Fro88er]

Tom, I am getting an error message. 

[Fro88er]

Sorry, here is some sample data.  All fact tables are joined to the DimCalendar. 

PDV Sample.xlsx

[TomMartens]

Hey @Fro88er ,

it's difficult to use your attached xlsx, as it just contains a single sheet, for this reason it's not possible to create a data model that represents your data model and where a calendar table is joined to multiple fact tables.

Nevertheless, I spotted 2 typos (missing brackets) in the measure above, give this version a try:

the average = 
var __currendate = MAX( 'DimCalendar'[Date] )
var dates = 
SELECTCOLUMNS(    
    TOPN( 
        5
        , FILTER( ALL( 'DimCalenar' ) , 'DimCalendar'[Date] <= __currendate && 'DimCalendar'[IsABillingDay] = 1 ) 
        , INT( 'DimCalendar'[Date] ) 
        , DESC 
    )
    , "Date" ,  'DimCalendar'[Date]
)
return
CALCULATE(
    AVERAGE(...[PDV Combined Total] )
    , dates
)

Make sure that you replace the 3dots the table reference in this line AVERAGE(...[PDV Combined Total] ) with the table name if [PDV Combined Table] is a column or simply delete the dots if it's a measure.

Regards,

Tom