topic Re: Calculate average on the last 10 dates/records in table in DAX Commands and Tips

Calculate average on the last 10 dates/records in table

kjesan — Wed, 12 Aug 2020 13:46:08 GMT

Hi,

Im trying to calculate average from the last 10 dates/records in my table. The table does not have consistent dates. I have created this measure but it only gives me 10 days before and up to current date and not the last 10 dates/records. Any idea how to solve this?

Measure:=CALCULATE(AVERAGE(OS[Sales]);DATESINPERIOD(OS[Date];LASTDATE(OS[Date]);-10;DAY))

Thanks in advance!

Re: Calculate average on the last 10 dates/records in table

amitchandak — Wed, 12 Aug 2020 14:37:05 GMT

kjesan , try with a date calendar

example

Rolling 10 day = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX(Sales[Sales Date]),-10,Day))

Rolling 10 day = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-10,Day))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184

Appreciate your Kudos.

Re: Calculate average on the last 10 dates/records in table

kjesan — Wed, 12 Aug 2020 17:47:41 GMT

amitchandak thanks for your reply!

I already did all those thing you suggest, but the main problem is that the dates I have in my fact table is only workdays and sometime not all workdays in a week. So I would really need it to calculate -10 dates from my fact table and not 10 days from current date. E.g. 10 days from today will look like this: 12.08.20 - 10 days = 02.08.20 . But in my result I dont have weekends so I will only have 8 records instead of 10 in this measure.

Any ideas how to solve this?

Re: Calculate average on the last 10 dates/records in table

Anonymous — Wed, 12 Aug 2020 20:28:07 GMT

// To do time-intel calculations // you HAVE TO have a Dates table // in the model. So, please // create one according to the guidelines // and connect to your fact table OS. // This table should have a column that // tells us if a day is a workday or // a weekend day. Let's say the field // is [Day Type] and it holds 2 values // "Weekday" and "Weekend". // Then your measure would be: [Measure] = var __totalDaysToAvgOver = 10 var __lastDate = MAX( Dates[Date] ) var __period = // You want to get 10 working // days starting from __lastDate // and going back. TOPN(__totalDaysToAvgOver, FILTER( ALL( Dates ), Dates[Date] <= __lastDate && Dates[Day Type] = "Weekday" ), Dates[Date], DESC ) var __shouldCalc = COUNTROWS( __period ) = __totalDaysToAvgOver var __result = if( __shouldCalc, AVERAGEX( __period, CALCULATE( 0 + SUM( OS[Sales] ) ) ) ) return __result

Re: Calculate average on the last 10 dates/records in table

mahoneypat — Thu, 13 Aug 2020 01:14:14 GMT

You can try an approach like this

Last 10 Days = VAR thisdate = MAX ( OS[Date] ) VAR last10days = TOPN ( 10, FILTER ( ALL ( OS[Date] ), OS[Date] <= thisdate ), OS[Date], DESC ) RETURN CALCULATE ( SUM ( OS[Sales] ), last10days )

If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.

Regards,

Pat

Re: Calculate average on the last 10 dates/records in table

kjesan — Thu, 13 Aug 2020 11:44:12 GMT

mahoneypat it works like a charm, thanks!

Re: Calculate average on the last 10 dates/records in table

Anonymous — Thu, 13 Aug 2020 12:25:11 GMT

kjesan

mahoneypat's solution is flawed. When you select a date that is 1st, 2nd,...,9th in your table, you will not get 10 last days but only 1, 2, 3...9 days since there's not enough days. If you then start to compare this measure among days, you'll get underestimated or exaggerated figures.

Re: Calculate average on the last 10 dates/records in table

Anonymous — Thu, 13 Aug 2020 12:26:42 GMT

Also, what will happen if on a working day (or more such days), there are no entries in the table? You'll be skipping such a day and reaching out for many more days. Is this what you want?

Re: Calculate average on the last 10 dates/records in table

kjesan — Thu, 13 Aug 2020 13:04:03 GMT

Anonymous - appreciate your reply here.

That is exactly what I want. I want the last 10 dates, not days. To illustrate you can see example in screenshot below where the 10 days average for 13th of august is 855,933 . You can see the values from fact table are not from consistent dates but the last 10 dates.

Re: Calculate average on the last 10 dates/records in table

Anonymous — Thu, 13 Aug 2020 13:32:27 GMT

That's what I was saying. I'm talking about the last 10 dates, not days. Still, if you read my comment well, you'll notice that the solution given by mahoneypat is flawed. If you are taking dates that are too close to the beginning of your table, you'll end up with fewer dates(!) than 10 and you'll be thinking everything's OK.

Re: Calculate average on the last 10 dates/records in table

kjesan — Thu, 13 Aug 2020 13:50:34 GMT

Anonymous not sure if I understand exactly what you are trying to say. I have tested manually calculation to confirm that the result is correct based on what I want, and it is.

Re: Calculate average on the last 10 dates/records in table

Anonymous — Thu, 13 Aug 2020 14:13:40 GMT

kjesan,

Never mind. You'll find out what's wrong when you try to calculate 10-date average for the very first dates in your table.