Allexcept and slicer

[Anonymous]

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:

test = CALCULATE ( measure, ALLEXCEPT ( 'Calendar', 'Calendar'[Year] ))

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

[VijayP]

@Anonymous 

Calculate(Measure,DATESBETWEEN(Dates[Date],MIN(Dates[Date]),MAX(Dates[Date])))

Please try this! Please share your Kudoes!

Vijay Perepa

[Anonymous]

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! 👍

[Anonymous]

hello @VijayP 

added data and desired result in the heading.

Could you please lend a hand?

[VijayP]

@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

[Anonymous]

@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.

[VijayP]

@Anonymous 

https://drive.google.com/file/d/17KnLGewTPZYq3pCqj3zFi_FGUfwmJeDe/view?usp=sharing

I think it should work

[Anonymous]

@VijayP  

no, sir, it doesn`t (

if works well when u choose all months

but it doesn`t , when u use date filter:

[VijayP]

@Anonymous 

Its working for me.

[Anonymous]

thats what iam talking about @VijayP 

[VijayP]

@Anonymous 

Will this Do?

[Anonymous]

@VijayP  looks similar.

could u please share the pbix ? 

[VijayP · Accepted solution]

@Anonymous 

https://drive.google.com/file/d/1STDGBeSwvVf6ZUHqHOSFK4TYXmSmDlim/view?usp=sharing

Please share Kudoes and Please mark this as solution

[Anonymous]

@VijayP  thank you so much!

its work!

[VijayP]

@Anonymous 

Please share your Kudoes, Many if possible hahahahah 😂

[VijayP]

@Anonymous 

I thinks this is the approach. 

[Anonymous]

@VijayP

i use such formula as measure:

SUMX(SUMMARIZE(table_1, object), CALCULATE(AVERAGEX('Calendar', CALCULATE(SUM(value), filter(table_2, category)))))

so, I get Average by columns and sum by rows.

What should I add to this formulla to get average value in every column ?

As I told before CALCULATE ( measure, ALLEXCEPT ( 'Calendar', 'Calendar'[Year] )) works good, but do wrong calculation when using dynamic month filter

Thats the problem

[amitchandak]

@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])))

[Anonymous]

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.