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
kjesan
Regular Visitor

Calculate average on the last 10 dates/records in table

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!

1 ACCEPTED SOLUTION
mahoneypat
Employee
Employee

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

11 REPLIES 11
mahoneypat
Employee
Employee

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


@mahoneypat it works like a charm, thanks!

Anonymous
Not applicable

@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.
Anonymous
Not applicable

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?

@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.

 

kjesan_0-1597323482330.png

 

Anonymous
Not applicable

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.

@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. 

Anonymous
Not applicable

@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.
amitchandak
Super User
Super User

@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-Y...


Appreciate your Kudos.

@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?

Anonymous
Not applicable

// 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

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.

Top Solution Authors