Calculate sum of column based on condition & with date slicer

[parames]

Team,

Here is my table:

1. Calendar(Date)

2. DataTable(ID, InvoiceDate, PaidDate, Amount)

Relationship(1:N) - Calendar[Date] to DataTable[PaidDate] 

On PowerBI Desktop, I've a filter for Calendar[Date] and selected for Jan 1, 2019 to Mar 31, 2019

DataTable (Sample Records)

(ID, InvoiceDate(DD/MM/YYYY), PaidDate(DD/MM/YYYY), Amount)

(1, 15/12/2018, 25/12/2018, 1000)

(2, 25/12/2018, 15/1/2019, 2000)

(3, 15/1/2019, 15/1/2019, 3000)

(4, 15/2/2019, 20/3/2019, 4000)

Condition: If DataTable[InvoiceDate] < Calendar[Date]; avoid those records (ID = 3,4 are only selected)

Output: 7000 (3000+4000)

The solution could be in the form of calulated measure or whichever easy to understand.

Thanks in advance.

Regards,

Param

@Ashish_Mathur  : I've been following you on this community. Can you help me to solve this?

[amitchandak]

As your dates are on DD/MM/YYYY format , make sure they are detected as date in model view . If does not work out create new date using this formula and use those 

Combine = mid(DD__MM__YY[Date],4,2) &"/"& left(DD__MM__YY[Date],2) & "/" & right(DD__MM__YY[Date],4) & " "& DD__MM__YY[ time]

This formula should work

Formula =
_min_date = min(Calendar[Date]) // Or min(selected(Calendar[Date]))
return
calculate(sum(DataTable[Amount]), DataTable[InvoiceDate] <= _min_date

[parames]

@amitchandak : This measure works good. Thanks.

Can you just let me know how to handle DataTable[InvoiceDate] = blank ?

There are few records where DataTable[InvoiceDate] is blank and all these records are also to be included for calculation of sum(DataTable[amount])

1. Calendar(Date)

2. DataTable(ID, InvoiceDate, PaidDate, Amount)

Relationship(1:N) - Calendar[Date] to DataTable[PaidDate] 

On PowerBI Desktop, I've a filter for Calendar[Date] and selected for Jan 1, 2019 to Mar 31, 2019

DataTable (Sample Records)

(ID, InvoiceDate(DD/MM/YYYY), PaidDate(DD/MM/YYYY), Amount)

(1, 15/12/2018, 25/12/2018, 1000)

(2, 25/12/2018, 15/1/2019, 2000)

(3, 15/1/2019, 15/1/2019, 3000)

(4, 15/2/2019, 20/3/2019, 4000)

(5, <blank>, 13/1/2019, 5000)

Condition 1: If DataTable[InvoiceDate] < Calendar[Date]; avoid those records

Condition 2: If DataTable[InvoiceDate] = <blank>; consider those records for sum calculation

(ID = 3,4,5 are only selected)

Output: 12000 (3000+4000+5000)

Thanks in advance.

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.