Need DAX measure to calculate open amount

[Erwin]

Hi,

I have a dataset comprised of rows of invoiced/paid amounts and booking dates. My goal is to get an historic overview of open amounts. Therefore I need to calculate the open amount up to a given date. 

I can't use a calculated column because I need to slice the information further to be able to show open amounts by company and customer. I'm going for a measure using DAX.

This is a sample of my dataset called FIAR_C03. I've also added the expected result of the measure:

Booking date | Amount       | Measure result

30-11-2016              -                               -

01-12-2016       1,000                        1,000 (also for dates in between)

05-12-2016       5,000                        6,000

06-01-2017       3.000                        9,000

12-01-2017      (1,000)                       8,000

15-01-2017      (3,000)                       5,000

02-02-2017       1,500                        6,500

04-02-2017      (5,000)                       1,500

I've solved this in Excel using simple SUMIF function. I just can't seem to get it to work in Power BI Desktop using DAX. I've created a formula for calculating one specific date, the issue is making PBI calculate for all dates in the given date range. This is what I got so far:

Open amount = CALCULATE(SUM(tblFIAR_C03[Amount]),FILTER(tblFIAR_C03,tblFIAR_C03[Booking date]<=DATE(2016,12,1)))

This formula works, but only for the given date of 1-12-2016. I can't seem to find a way to make it calculate for any given date range.

Please provide input on this matter, I'm kinda stuck here

Rg. Erwin

[Phil_Seamark · Accepted solution]

Here is a measure which might be close to what you need.

I assume you have a relationship between your tblFIAR_C03 table to a Date table.

Open Amount = CALCULATE(
                    SUM(
						'tblFIAR_C03'[Amount]),
						FILTER(
							ALL('Dates'[Date]),
							'Dates'[Date] <= MAX('Dates'[Date])
							)
						)

[Erwin]

Hi Phil,

Thanks a lot for your suggestion, this works like a charm! On to the next challenge!

Rg. Erwin