Calculate sum of and between dates

[afwork]

Hello,

Can you please help me with this?

I want a measure that calculates the sum of Apples between 01/01/2024 to 29/02/2024 (end date dynamic based on slicer selected).

I started with this but it doesn't work.

Thank you

Apple YTD =
CALCULATE(SUM(PL_Transactions[AccountingAmt]), PL_Transactions[Type] = "Apple", DATESBETWEEN(PL_Transactions[AccountDate],DATE(SELECTEDVALUE(PL_Transactions[AccountDate].[Year]),1,1),EOMONTH(DATE(SELECTEDVALUE(PL_Transactions[AccountDate].[Year]),SELECTEDVALUE(PL_Transactions[AccountDate].[MonthNo]),1),0)))

Type	AccountingAmt	AccountDate
Orange	63903	1/01/2024
Apple	689762	8/01/2024
Orange	797423	15/01/2024
Apple	243368	22/01/2024
Apple	304468	29/01/2024
Apple	674603	5/02/2024
Orange	381344	12/02/2024
Orange	303282	19/02/2024
Orange	829333	26/02/2024
Apple	72945	4/03/2024
Orange	317864	11/03/2024
Answer	1912201

[Ritaf1983 · Accepted solution]

Hi @afwork 
As a first step create a dim date table, it will help you with this issue anf for future analyses :

more information about dim date tables and why you should use them here :

https://blog.coupler.io/power-bi-calendar-table/

2. create a relationship with your main table by date

3. create a measure :

apples sum =

var start_ = date(2024,01,01)

var end_ = max('DimDate'[Date])

RETURN

CALCULATE(SUM('Table'[AccountingAmt]),ALL(DimDate[Date]),'Table'[Type]="apple",DATESBETWEEN('DimDate'[Date],start_,end_))

Result :

The pbix is attached

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

[afwork]

How to make this prior year please:

var end_ = max('DimDate'[Date])

[Ritaf1983]

Hi @afwork 
var end_ = max('DimDate'[Date])-365

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

[afwork]

Thank you! This worked as intended.

[Ritaf1983]

Happy to help💗

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[afwork]

I don't want a slicer for Apple, only for date please.

Thank you!

[Ashish_Mathur]

File attached.