Total sum error with sum filters

[Anonymous]

Hello everybody,

I'm having problems summing the total of the tables, I have 2 products that have 2 release dates, it was launched with a quantity on each date, then I need to know how its sales performance was, but for example, I have a sales total in a period 636 sales.

I'm using this measurement:

Varejo 4 Sem =

   VAR DataLancamento = MAX(Lancamento_Vermelha[DT_Lançamento])

   VAR DataMax = DataLancamento + 41

   VAR DataMin = DataLancamento + 41

RETURN

    CALCULATE(

        [Venda Fisica],

  DATESBETWEEN(Calendario[Data],MIN(Lancamento_Vermelha[DT_Lançamento])-41,MAX(Lancamento_Vermelha[DT_Lançamento])+41)))

[v-yangliu-msft · Accepted solution]

Hi  @Anonymous ,

Here are the steps you can follow：

1. Create measure.

Measure_Varejo 4 Sem =
var _table1=
SUMMARIZE('Lancamento_Vermelha','Lancamento_Vermelha'[DT_Lancamento],"Value",[Varejo 4 Sem])
return
IF(
    HASONEVALUE('Lancamento_Vermelha'[DT_Lancamento]),
    [Varejo 4 Sem],
    SUMX(_table1,[Value]))

2. Result:

Best Regards,

Liu Yang

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

[tamerj1]

Hi @Anonymous 
Please try the following

Varejo 4 Sem =
SUMX (
    VALUES ( Lancamento_Vermelha[Material Pai] ),
    CALCULATE (
        VAR DataLancamento =
            MAX ( Lancamento_Vermelha[DT_Lançamento] )
        VAR DataMax = DataLancamento + 41
        VAR DataMin = DataLancamento - 41
        RETURN
            CALCULATE (
                [Venda Fisica],
                DATESBETWEEN ( Calendario[Data], DataMin, DataMax )
            )
    )
)

[Anonymous]

It still hasn't happened, in fact I think the error is in the VAR DataLaunching, because if I put MAX, it brings me the value of 08/22/2021, and if I put the minimum it brings me the value of 11/15/2021 .

how do i return exactly then the date in question?

[tamerj1]

@Anonymous 
would you please copy/paste the code so I can edit?

[Anonymous]

sorry, how do i do that? what code ?

[tamerj1]

@Anonymous 
The [Varejo 4 Sem] measure. Please copy the dax formula and paste it in a reply so I can edit.

[Anonymous]

Atacado 4 Sem = 
   VAR DataLancamento = MAX(Lancamento_Vermelha[DT_Lançamento])
   VAR DataMax = DataLancamento + 28
   VAR DataMin = DataLancamento - 41
RETURN
    SUMX(
        VALUES(Cadastro_Material[Material Pai]),
    CALCULATE(
        [Venda Fisica Atacado],        DATESBETWEEN(Calendario[Data],MIN(Lancamento_Vermelha[DT_Lançamento])-41,MAX(Lancamento_Vermelha[DT_Lançamento])+28)))

so ?

[tamerj1]

@Anonymous 

Please try

Atacado 4 Sem =
SUMX (
    SUMMARIZE (
        Cadastro_Material,
        Cadastro_Material[Material Pai],
        Cadastro_Material[DT_Lancamento]
    ),
    CALCULATE (
        VAR DataLancamento =
            MAX ( Lancamento_Vermelha[DT_Lançamento] )
        VAR DataMax = DataLancamento + 28
        VAR DataMin = DataLancamento - 41
        RETURN
            CALCULATE (
                [Venda Fisica Atacado],
                DATESBETWEEN ( Calendario[Data], DataLancamento, DataMax )
            )
    )
)

[Anonymous]

It kind of worked, but now when I talk about big dates it brings me a very large amount of the released,

Do I have to do something to that extent for it to get a filter?

Or for example, I have a release on 08/01/2022, so I go ahead and go back 41 days, which is between the period we did in the measure of DataMin, DataMax.

I just apply a filter of Year and month times.

On 01/08/2002 I launched 27800, but it is saying that I sold 282,924, even though the field Cadastro_Material[Material Parent] would be the product code.

I'm sorry for this.

[tamerj1]

@Anonymous 

I'm sorry but I don't think I properly understand.