Forum Discussion

ngomes's avatar
ngomes
Helper II
3 years ago
Solved

DAX/ Matrix

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

Need Help
Show and Tell
  • ngomes's avatar
    ngomes
    3 years ago

    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 🙂

5 Replies

    • ngomes's avatar
      ngomes
      Helper II
      3 years ago

      hello Greg

       

      column titles come from measurements

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        3 years ago

        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.