Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
Hello, guys!
I think the solution is not difficult, but I still can't find it. Could you help me?
The task - find measure average by filtered date perion in pivot table by each objects (object - rows, month - columns).
For this, i use such formula:
and it works fine, till i have to use month slicer to change full year to 10 month, for example.
The "test" still calculate the whole year average.
How could I avoid it? And get dynamic average, depends on choosing months?
Lind to db: https://gofile.io/d/VLBPTf
How it should be (11 months):
Avg sales between dates |
25 849,87 |
25 849,87 |
25 849,87 |
25 849,87 |
25 849,87 |
25 849,87 |
25 849,87 |
25 849,87 |
25 849,87 |
25 849,87 |
25 849,87 |
Solved! Go to Solution.
@Anonymous
https://drive.google.com/file/d/1STDGBeSwvVf6ZUHqHOSFK4TYXmSmDlim/view?usp=sharing
Please share Kudoes and Please mark this as solution
Proud to be a Super User!
@Anonymous
Calculate(Measure,DATESBETWEEN(Dates[Date],MIN(Dates[Date]),MAX(Dates[Date])))
Please try this! Please share your Kudoes!
Vijay Perepa
Proud to be a Super User!
hi @VijayP
nice to see you
i tried it. This formula gives the each month measure.
However, I need to find average measure on choosing period using slicer.
Ok, I will prepare data.
@Anonymous
Try that measure what you calculate is showing Average by using AVERAGE or AVERAGEX function and then incorporate in my Formula and hope that works! 👍
Proud to be a Super User!
@Anonymous
https://drive.google.com/file/d/1dhaLDcmJaJ4v-0iTTKV77sKtgfcm0V-l/view?usp=sharing
Please find attached file with solution. let me know whether you are looking for this
Proud to be a Super User!
@VijayP https://gofile.io/d/uBbbpZ
no, it doesn`t work (
It should works like "Average sales per FY" but be date filter depended.
For example, if you choose 1-11 months,
it should be 25 849,87 in every cell.
@Anonymous
https://drive.google.com/file/d/17KnLGewTPZYq3pCqj3zFi_FGUfwmJeDe/view?usp=sharing
I think it should work
Proud to be a Super User!
no, sir, it doesn`t (
if works well when u choose all months
but it doesn`t , when u use date filter:
@Anonymous
Its working for me.
Proud to be a Super User!
@Anonymous
Will this Do?
Proud to be a Super User!
@Anonymous
https://drive.google.com/file/d/1STDGBeSwvVf6ZUHqHOSFK4TYXmSmDlim/view?usp=sharing
Please share Kudoes and Please mark this as solution
Proud to be a Super User!
@Anonymous
Please share your Kudoes, Many if possible hahahahah 😂
Proud to be a Super User!
@Anonymous
I thinks this is the approach.
Proud to be a Super User!
i use such formula as measure:
@Anonymous , Try if one of these can work
This year = CALCULATE([Measure],DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
This Year = CALCULATE([Measure],filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
hi @amitchandak
there are no working formulas, unfortunately.
And moreover, it shouldn't be used only for current/last year.
Cause, pivot table is filtered by years also.
User | Count |
---|---|
90 | |
88 | |
87 | |
79 | |
49 |
User | Count |
---|---|
153 | |
145 | |
106 | |
74 | |
55 |