Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to Solution.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@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.
@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.
@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?
// 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
User | Count |
---|---|
60 | |
22 | |
18 | |
18 | |
16 |
User | Count |
---|---|
87 | |
59 | |
52 | |
38 | |
21 |