cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Fro88er
Helper III
Helper III

Running Total, last 5 BILLABLE days from today, rolling

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?

 

PDV BillableDays.JPG

 

 

 

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
1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

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.

Untitled.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

8 REPLIES 8
Fro88er
Helper III
Helper III

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

 

Thank you so much,
Mark

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Ashish_Mathur
Super User
Super User

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.

Untitled.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

TomMartens
Super User
Super User

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Tom, I am getting an error message. 

 

PDV BillableDays2.JPG

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

 

PDV Sample.xlsx

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!