Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I'm trying to calculate rolling average, but facing problem when year changes.
It's considering previous year while calculation.
Finding rolling average of "Cost per pair -USD" and Costper pair usd = [Total Epenses] / [QTY]
It is showing .21 (Rolling Avg.) instead of .18 (Year changes, So fresh cal.), higlighted with red line. It is because of considering previous month result
I used Quick measure in power bi for rolling average.
Rolling Avg. = IF(
ISFILTERED('Dummy_Table'[ETA]),
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('Dummy_Table'[ETA].[Date])
VAR __DATE_PERIOD =
DATESBETWEEN(
'Dummy_Table'[ETA].[Date],
STARTOFMONTH(DATEADD(__LAST_DATE, -12, MONTH)),
__LAST_DATE
)
RETURN
AVERAGEX(
CALCULATETABLE(
SUMMARIZE(
VALUES('Dummy_Table'),
'Dummy_Table'[ETA].[Year],
'Dummy_Table'[ETA].[QuarterNo],
'Dummy_Table'[ETA].[Quarter],
'Dummy_Table'[ETA].[MonthNo],
'Dummy_Table'[ETA].[Month]
),
__DATE_PERIOD
),
CALCULATE([Cost per Pair], ALL('Dummy_Table'[ETA].[Day]))
)
)
Expected result.
When I tried in excel it look like. It was just simply with formula.
Instead of .21 I want .18, as year changes from 2017 to 2018.
Solved! Go to Solution.
Hi,
You may download my PBI file from here.
Hope this helps.
Hi,
Assuming the following, try this measure:
=CALCULATE([Cost per pair - USD],DATESYTD(Calendar[Date],"31/12"))
If this does not get the correct answer, then share the link from where i can download your PBI file.
Hi Sorry for replying late .
Here is dummy file..
https://drive.google.com/open?id=16pMRCfKbzWzZzQkDBr4l-eaGy6kxIFpL
Hi,
You may download my PBI file from here.
Hope this helps.
You are welcome.
Hi dheer1688,
Which DAX formula are you using on excel side? And which one is your expected result?
Regards,
Jimmy Tao
Hi,
I used formula in excel.
Expected result :
Instead of .21 for Jan2018 it should show .18 as year changes from 2017 to 2018.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |