DAX/ Matrix

[ngomes]

Good afternoon everyone

I need your help for the following

I have a report in which the objective is to compare the quantities sold between the selected years.

I select the years
Filter

I have 2 measurements:

1st collects the quantities of the selected maximum year

year N =

VAR  MAXT=MAXX(Database,Database[Data].[Ano])  

RETURN  CALCULATE(SUM(Database[Quantidade]),FILTER(Database,Database[Data].[Ano]=MAXT))

2nd collects the quantities of the selected minimum year

year N-1 =

VAR  MiNT=MINX(Database,Database[Data].[Ano])  

RETURN  CALCULATE(SUM(Database[Quantidade]),FILTER(Database,Database[Data].[Ano]=MiNT))

I noticed that in the 1st year the same amount is returned as in the 2nd year, when the 1st year has no amounts

I only have values in the 2nd year...

has anyone had the same problem?

Attention that the objective is not to compare the quantities with the previous year, the user chooses which years to compare, in the report the maximum year and the minimum year chosen by the user

[Greg_Deckler]

@ngomes Where are your column headings coming from? 1 year and 2 year?

[ngomes]

hello Greg

column titles come from measurements

[Greg_Deckler]

@ngomes Reading this a little closer, it makes sense what is going on. If your Ano slicer is coming from a date table for example and this is filtering your fact table and you have no rows for 2022 then getting the min and max year out of that table is going to result in the same year. Because 2021 and 2022 will filter the table down to just 2021 rows (no 2022 rows) but you are getting the MIN and MAX from the fact table it looks like versus you should probably be getting that from whatever table drives your slicer.

[ngomes · Accepted solution]

I ended up creating a calendar table and connecting that table with the data one, I changed the formula date filters to the new calendar table and it worked with the max and min of years

thanks for the help and until next time 🙂

[ngomes]

It makes sense what you say Greg

so should I create a condition that only displays values when the max and min years are different or is there another way to get around this problem?

thanks for the great help