Calculate sum() according to a date range

[Nissa23]

Hello,

Here is a sample of my table :

ID	Name	Start_date	End_date	Current	value
1	A	1999-01-01	2999-01-01	1	15
2	B	1999-01-01	2023-03-22	0	10
3	B	2023-03-22	2999-01-01	1	20
4	C	1999-01-01	2999-01-01	1	35
5	D	1999-01-01	2023-06-25	0	10
6	D	2023-06-25	2999-01-01	1	63

I have a linked table that contains a date : Validate_date

I want to calculate a SUM(Value) where valide_date falls between Start and End_date.

For Example : for 😧 if Validate_date is between 1999-01-01 & 2023-06-25 I use 10 in the calculated measure, otherwise, 63.

I've tried to calculate a new column as follow :

Total_Value = 
IF (SELECTEDVALUE('Tabl1'[Validate_date]) >= SELECTEDVALUE(Tab2[Start_date]) && SELECTEDVALUE('Tabl1'[Validate_date])<SELECTEDVALUE((Tab2[End_date]),1,0)
)

I got only 0 . So doesn't work.

Can anyone help on how to calculate this measure?

Thanks a lot.

Nissa

[v-tianyich-msft · Accepted solution]

Hi @Nissa23 ,

I made simple samples and you can check the results below:

An attachment for your reference. Hope it helps!

Best regards,
Community Support Team_ Scott Chang

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

[Nissa23]

Thank you very much @_AAndrade .

It's working.

Regards,

[_AAndrade]

Hi @Nissa23,

Could you please share a picture of your validate_date table and the relationship between both tables?

[Nissa23]

Hi @_AAndrade 

My model is a bit complicated. I've tried to built a sample of my data in order to simplify the problem.

The relationship between the 2 tables is is as shown bellow

Hope its will help you to understand the issue.