Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to 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.
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.
You are welcome.
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.
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
Tom, I am getting an error message.
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
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |