Forum Discussion
Allexcept and slicer
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 |
Anonymous
https://drive.google.com/file/d/1STDGBeSwvVf6ZUHqHOSFK4TYXmSmDlim/view?usp=sharing
Please share Kudoes and Please mark this as solution
19 Replies
- amitchandak
Super User
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.
- VijayP
Community Champion
Anonymous
Calculate(Measure,DATESBETWEEN(Dates[Date],MIN(Dates[Date]),MAX(Dates[Date])))
Please try this! Please share your Kudoes!
Vijay Perepa
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.
- VijayP
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! 👍
