Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Fro88er
Helper IV
Helper IV

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 IV
Helper IV

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/
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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.