Calculate ignoring the date and adding up all values instead of the line values

[Anonymous]

Hello, I have the table  below  with a bunch of columns, each line gives me a product and on another non related table I have the correspondency  (from-to) of each of theses products ids and their targets.

I am using the fofllowing DAX to return the "venda categoria espelho"

Venda Categoria Espelho =

VAR codespelho = MAX('Produto espelho'[cod_target]) -- reads that from to table and returns the code of the corresponding item

RETURN

CALCULATE(SUM('Venda Diária'[venda_brut]),

            FILTER(

                   ALL('Venda Diária'),                                        -- clears all filters from the table and gives me the SUM using codespelho

                   'Venda Diária'[codpro] = codespelho  

           )

)

However as you can see it messes up because it is ignoring the outer filter of date (which is set to march 2021and is also displayed on one of the columns on the table as well) and because of that, it not only fills the table with non filtered data (each row shown after  "elim.odor..." is outside the daterange set by the outer filter) but also, per row it is returning me the SUM of all dates instead of only the date defined by that row.

I understand the it is because when filtering for the whole table I end up removing all filters, but i cant get this to work an other way.

Help Appreciated

[Anonymous · Accepted solution]

Managed to make it work, in the end I ended up clearing all filters, creating some variables to store the data I needed for the filters, adding those variables on the tables(date and "venda diária") by splitting the date column into year and another column for month. And using those variales as joinned arguments on the filter function using the AND function (&&)

[AlexisOlson]

Instead of clearing all filters on the table with ALL('Venda Diária'), maybe try only clearing the specific filters you do want to be cleared.

[Anonymous]

I want to kee just the date, but i cant get REMOVEFILTERS to work

[Anonymous]

By adding another condition to the filter i get rid of the extra lines, but the values are still wrong for some reason it is summing up the full period

Venda Categoria Espelho =

VAR codespelho = MAX('Produto espelho'[cod_target])

VAR datax = MAX('Venda Diária'[data])

RETURN

CALCULATE(SUM('Venda Diária'[venda_brut]),

FILTER(

all('Venda Diária'),

'Venda Diária'[codpro] = codespelho&&

datax

)

)