Show in a Table totals for max and min Year Selected in Filter..

[AndreaPRJ]

..regardless of the year added in the tablecolumn.

Hi to all!

In my report I have a total of 6.594 calls divided as follows per year:

2019: 1138 

2020: 1535

2021: 2376

2022: 1545

I have a slicer with which the user can choose two years to compare and one simple table with these measures:

TotalCalls:= COUNT(CALLS[NUMEROSTEP])

MaxYear:=CALCULATE(TotalCalls;

    FILTER (ALLSELECTED(CALLS[DATE]);

    year(CALLS[DATE]) = [Max Year Selected]))

MinYear:=CALCULATE([TotalCalls];

    FILTER (ALLSELECTED(CALLS[DATE]);

    year(CALLS[DATE]) = [Min Year Selected]))

Max Year Selected = Max(CALLS[DATE].[Year])

Min Year Selected = Min(CALLS[DATE].[Year])

with NO filter selected:

If I choose two years from the slicer, all ok! and I see 2.376 under Max Year and 1.535 under Min Year 

 However, if I add the year ("Anno column in italian..)  in front of the existing fields in my table, the data begins to not come out as I would like, because I imagine the filter context of the year is added

In the firt raw (YEAR 2020), I would like to see 2.376 under Max Year and 1.535 under Min Year

In the second raw (YEAR 2021), I would like to see the same

I tried with REMOVEFILTERS ,ISINSCOPE and others but i did not solve 😞

I'm just starting out with dax

The final objective will then be to show the data in a matrix with the Year \ Month structure on the left and as values ​​the two measures with the calls of the year max and year min

PS: how could I limit the choice in the year filter to ONLY two years and no more than two?

THANK YOU VERY MUCH in advance

[AndreaPRJ]

hi amitchandak, first of all thank you.

Unfortunately with your two measures I would get this .. with "holes".

I would ALWAYS want both columns full, where the first shows me the number of calls for the maximum year among those selected and the second for the minimum year.

PS: the second option of your measurements would remove a couple of other active filters at the report level and therefore I do not take it into consideration

Bye

[v-xiaotang · Accepted solution]

Hi @AndreaPRJ 

You can try this, create the measures below,

Max Year Selected = MAXX(ALLSELECTED('Table'),'Table'[Year])

Min Year Selected = MINX(ALLSELECTED('Table'),'Table'[Year])

MaxYear = CALCULATE(MAX('Table'[Qty]),FILTER(ALL('Table'),'Table'[Year]=[Max Year Selected]))

MinYear = CALCULATE(MAX('Table'[Qty]),FILTER(ALL('Table'),'Table'[Year]=[Min Year Selected]))

result

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[AndreaPRJ]

Thank you very much and very sorry for the delay!

[amitchandak]

@AndreaPRJ , try like

MaxYear:=
Var _max = maxx(allselected('CALLS') , 'CALLS'[Year])
return
CALCULATE(TotalCalls;
FILTER (CALLS; year(CALLS[DATE]) = _max))

MinYear:=
Var _max = minx(allselected('CALLS') , 'CALLS'[Year])
return
CALCULATE(TotalCalls;
FILTER (CALLS; year(CALLS[DATE]) = _max))

or

MaxYear:=
Var _max = maxx(allselected('CALLS') , 'CALLS'[Year])
return
CALCULATE(TotalCalls;
FILTER (all(CALLS); year(CALLS[DATE]) = _max))

MinYear:=
Var _max = minx(allselected('CALLS') , 'CALLS'[Year])
return
CALCULATE(TotalCalls;
FILTER (all(CALLS); year(CALLS[DATE]) = _max))