sum columns between 2 dates

[DiePic]

Hi all

I've this table

I need to have a column or a measure that sum for every "customer no" and between 2 dates selected, the columns with amount (SALDO).

[vk_pbi]

Hi,
If you need static table.. then you can use power query group by function to generate a new table 

[DiePic]

Maybe it's not the best way and if some expert will suggest another idea, it will be apprecied, because in that way, I've a file with double weight.
.....
I solved duplicating the query/table "Movimenti" in "Movimenti2" and  I've applied the measure on the 2nd table ... in this way it's working for me.

[ryan_mayu]

@DiePic 

why not use the slicer date between?

it will give you the sum of amount of that period

[DiePic]

Hi @ryan_mayu and thank you for your reply

your suggestion is perfect for the 1st part of my goal.

now I need to insert a column that contain for every "customer code" (1st column):
(sum AVERE) - (sum DARE) for the period previous the 1st data in the slicer.

There is a dax formula or another way to do that?

....
I try with this dax formula but resul is all fields blanks

CALCULATE (

  SUM ( Movimenti[SALDO] ),

    FILTER (

      Movimenti,

      Movimenti[Customer No_] = [SelectedCliente]

      && Movimenti[Accounting Date] < [DataInizio]

    )

)

... where [SelectedCliente] and [DataInizio] are 2 measures that working
[DataInizio] = DATEVALUE(CALCULATE(MIN('Movimenti'[Accounting Date]),ALLSELECTED(Movimenti)))

[SelectedCliente] = SELECTEDVALUE(Movimenti[Customer No_])

[ryan_mayu]

could you pls provide sample data (not the screenshot) and the expected output. I can't find the soution based on the screenshots and DAX you provided

[DiePic]

Hi @ryan_mayu  ,

Thanks for reply and interest in my post.

Here a sample of my data and what I need to have:

Customer code	Customer name	DARE	AVERE	SALDO	Accounting date
01	CUSTOMER01	10	100	90	15/12/2022
01	CUSTOMER01	10	30	20	01/01/2023
01	CUSTOMER01	10	30	20	15/01/2023
02	CUSTOMER02	50	100	50	15/12/2022
02	CUSTOMER02	50	0	-50	01/01/2023
02	CUSTOMER02	10	110	100	15/01/2023
03	CUSTOMER03	20	20	0	15/12/2022
03	CUSTOMER03	30	100	70	01/01/2023
03	CUSTOMER03	20	0	-20	15/01/2023

Field SALDO is the difference between AVERE – DARE.

I need to know the sum amount of DARE-AVERE-SALDO between a period, for example from 01/01/2023 to 31/01/2023 and SALDO after 01/01/2023 (the 1st date entered).

This is the result that I need:

Customer code	Customer name	SALDO after	DARE between	AVERE between	SALDO between
01	CUSTOMER01	90	20	60	40
02	CUSTOMER02	50	60	110	50
03	CUSTOMER03	0	50	100	50

I hope to be clearer

[ryan_mayu]

@DiePic 

how to get the SALDO after?

why it is 90 for customer 1? the date for that is before 2023/1/1

[DiePic]

Hi @ryan_mayu 

I've written too fast and I've make some error.
You must take the sum of fields SALDO  after date 01/01/2023 and that is the "SALDO before" (I've wrong written "SALDO after").
"SALDO between" is the sum of all fileds "SALDO" between dates selected. 

[ryan_mayu · Accepted solution]

@DiePic 

Pls check if this is what you want

[DiePic]

Hi @ryan_mayu 

yes this is, now I need also the column "SALDO before".
I've do it but I find a way only using the same query 2 times and I would find a way to have the new column with only a query, because it contains more than 700.000 records and the weight of file is very important 🙂 .
As you can see in picture below, I need also another column that contain the "same" of SALDO  column (AVERE - DARE), but for all the records before the first date in selection....

[ryan_mayu]

@DiePic 

 i  didn't rename the measure, The Measure column is the SALDO before.

[DiePic]

Sorry @ryan_mayu  I havent seen it ... ok the formula in MEASURE is perfect for me 

thanks a lot

[ryan_mayu]

you are welcome

[DiePic]

Sorry @ryan_mayu  I havent seen it ... ok the formula in MEASURE is perfect for me

thanks a lot