cancel
Showing results for
Did you mean:
Highlighted
Helper IV

## 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:

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
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Solution Sage

19 REPLIES 19
Highlighted
Super User IV

## Re: Allexcept and slicer

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

Proud to be a Super User!

Highlighted
Helper IV

## Re: Allexcept and slicer

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.

Highlighted
Solution Sage

## Re: Allexcept and slicer

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

Vijay Perepa

Highlighted
Helper IV

## Re: Allexcept and slicer

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.

Highlighted
Solution Sage

## Re: Allexcept and slicer

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

Highlighted
Helper IV

## Re: Allexcept y slicer

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

Highlighted
Helper IV

## @Re: Allexcept and slicer

hello @VijayP

Could you please lend a hand?

Highlighted
Solution Sage

## Re: @Re: Allexcept and slicer

Please find attached file with solution. let me know whether you are looking for this

Highlighted
Helper IV

## Re: @Re: Allexcept and slicer

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.

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021