Forum Discussion
Cumulative average starting from zero every year
Hi im trying to calculate a cumulative average of the amount of cases closed by staff per week. I'm using a fiscal week calendar and want to start from ZERO every year.
Total cases are the amount closed in that week, staff is the count of staff linked to those cases. The average per week. Then I want a cumulative average working up to 52 weeks (or 53). then from week 1 of the following year start from zero and work up again.
This is the Dax code I have currently but its just not working and I cant work it out. Any help would be really appreciated 😀
CumAvg = AVERAGEX(DATESBETWEEN(DATES_W[End_date].[Date],MINX(ALL(DATES_W[FWeeknYear.2]),DATES_W[FWeeknYear.2]),MAX(DATES_W[FWeeknYear.2])),[AvgRelCASES])AvgRelCASES = AVERAGE(DATES_W[Rel_TotalCases])
Displaced_2000 , Assume Fiscal year start from April
You need measure like
YTD Sales = CALCULATE(AvergaeX(Values('Date'[Week Year]), CALCULATE(SUM(Sales[Sales Amount]))),DATESYTD('Date'[Date],"3/31"))
Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s
3 Replies
- amitchandak
Super User
Displaced_2000 , Assume Fiscal year start from April
You need measure like
YTD Sales = CALCULATE(AvergaeX(Values('Date'[Week Year]), CALCULATE(SUM(Sales[Sales Amount]))),DATESYTD('Date'[Date],"3/31"))
Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s thank you that has really helped. If I wanted the DATESYTD date to be dynamic depending on the year. eg each year the last day of the year moves its always the sunday before the first monday in April.
Could you give me some pointers to solve that issue?
Would dates between work? I've created a column which calculated the first day of the fiscal year and another which is the last day.
I'm trying to use DATESBETWEEN but it is giving me an error, it is expecting a single value not a column and mentions aggregation
CALCULATE(AVERAGEX(DimDate_W,(TOTALYTD(SUM(DimDate_W[Rel_ReletPeriod]),DimDate[Date]))),DATESBETWEEN(DimDate_W[Start of Week],DimDate_W[FiscalStartDate],DimDate_W[FiscalEndDate]))
