Forum Discussion
12 month rolling average - quick measure
Hello, I am trying to calculate the average staffing over a 12 month period so have used the new quick measure to generate this for me, however when I pull the calculation into the report it comes back blank, not sure what I am doing wrong here if anyone is able to suggest anything to look at. Thanks!
[Emp On Date] is a measure which generates me the number of employees active on that date based on their start and leave date
'Start_Dates'[Full Date] is a table of dates linking employees start/leave dates
Emp On Date rolling average =
IF(
ISFILTERED('Start_Dates'[Full Date]),
ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
VAR __LAST_DATE = ENDOFMONTH('Start_Dates'[Full Date].[Date])
VAR __DATE_PERIOD =
DATESBETWEEN(
'Start_Dates'[Full Date].[Date],
STARTOFMONTH(DATEADD(__LAST_DATE, -12, MONTH)),
__LAST_DATE
)
RETURN
SUMX(
CALCULATETABLE(
SUMMARIZE(
VALUES('Start_Dates'),
'Start_Dates'[Full Date].[Year],
'Start_Dates'[Full Date].[QuarterNo],
'Start_Dates'[Full Date].[Quarter],
'Start_Dates'[Full Date].[MonthNo],
'Start_Dates'[Full Date].[Month]
),
__DATE_PERIOD
),
CALCULATE([Emp On Date], ALL('Start_Dates'[Full Date].[Day]))
)
)1 Reply
- v-piga-msft
Resident Rockstar
Hi vagelisgeo,
however when I pull the calculation into the report it comes back blank,
This may be caused by the context you calculated.
From the information you provided, I cannot reproduce the issue as yours well , could you share a dummy pbix file or the data model which can reproduce this issue, so that we can help further investigate on it?
You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.)
In addition, you could have a reference of this article How can I calculate a rolling average in DAX/PowerBI?
Best Regards,
Cherry