If Invoice Date is less than selected date

[gauravnarchal]

I need help to create a measure!

If Invoice date is less than (Selected date) return total Invoice amount.

invoice date	Name	Company	InvoiceAmount
20-Jul-20	User1	ABC	200
25-Jul-20	User2	ABC	500
30-Jul-20	User3	ABC	800
04-Aug-20	User4	ABC	1100
09-Aug-20	User5	ABC	1400
10-Jun-20	User1	ABC123	1700
20-Jun-20	User2	ABC123	2000
30-Jun-20	User3	ABC123	2300
10-Jul-20	User4	ABC123	2600
20-Jul-20	User5	ABC123	2900
30-Jul-20	User6	ABC123	3200
09-Aug-20	User7	ABC123	3500
19-Aug-20	User8	ABC123	3800
10-Jun-20	User1	TEST1	4100
20-Jun-20	User2	TEST1	4400
30-Jun-20	User3	TEST1	4700
10-Jul-20	User4	TEST1	5000
20-Jul-20	User5	TEST1	5300
30-Jul-20	User6	TEST1	5600
09-Aug-20	User7	TEST1	5900
19-Aug-20	User8	TEST1	6200
29-Aug-20	User9	TEST1	6500
08-Sep-20	User10	TEST1	6800

[Pragati11]

HI @gauravnarchal ,

How you want this Selected Date value on yuor report? Will it come from a filter?

You haven't detailed your query enough.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Thanks,

Pragati

[gauravnarchal]

Hi @Pragati11  -  Selected date is measure. "SelectedDate = MIN('Calendar'[Date])"

I will use the Calendar Date in the slicer to get my results.

[amitchandak · Accepted solution]

@gauravnarchal ,

if date table is connected

new measure =
var _date = maxx(allselected(Date),Date[date])
return
calculate(Sum(Table[duration_alarms_7day]), filter(All(Table), Table[invoice date] < _date ))

if date table is not connected
new measure =
var _date = maxx(allselected(Date),Date[date])
return
calculate(Sum(Table[duration_alarms_7day]), filter(Table Table[invoice date] < _date ))

[gauravnarchal]

Thanks @amitchandak  I have used the below measure and it worked.

if date table is not connected
new measure =
var _date = maxx(allselected(Date),Date[date])
return
calculate(Sum(Table[duration_alarms_7day]), filter(Table Table[invoice date] < _date ))

Can you please help me to create a measure to get the result for the below:-

If Invoice date is Greater than (Selected date) return total Invoice amount.