Need DAX measure to calculate open amount (2nd attempt)

[Erwin]

Hi all,

Re below message, which was set to solved. I now have a more detailed request, which I can't seem to work out using DAX.

Need DAX measure to calculate open amount

Please review below eaxample of my data set:

In the previous message (see link above) Phil taught me to sum the Amount column in order to get an open amount by date. A summation of the Amount column is sufficient in this case because invoiced and paid amounts are in the same column.

CALCULATE(SUM('Table'[Amount]),FILTER(ALL('Calendar'[Date]),'Calendar'[Date] <= MAX('Calendar'[Date])))

Option #1 in the picture is the Excel equivalent of Phil's DAX formula.

To align with the way my company is used to calculate open amounts I need to have a more detailed calculation. This is option #2 in the picture. Basically the new calculation needs to sum the rows in Table which:

1. Doc type = "invoice"
2. Doc date <= key date
3. Clearing date >= key date OR =""

Option #2 in the picture is the way I worked this out in Excel.

Can you please provide info on how to achieve this using DAX?

Many thanks in advance!

Erwin

[kcantor]

@Erwin

You will need to wrap your measure inside calculate with additional filters.

Measure1 = CALCULATE(SUM('Table'[Amount]),FILTER(ALL('Calendar'[Date]),'Calendar'[Date] <= MAX('Calendar'[Date])))

Measure2 = CALCULATE([Measure1], 'Table'[Doc type] = "invoice", 'Table'[Doc date] >= [key date], 'Table'[Clearing date] >= [key date] || 'Table[Clearing date] = "")

Calculate lets you recalculate a measure (even one built on another calculate) to filter rows and data used. As I am not familiar with your past posting, I have built this based on using 'Table' as your table name. You will need to specify the table name from your source and the correct column headings. I also built this on the assumption that your first measure was correct as I am short on time but at least wanted to try to help you. Perhaps @Phil_Seamark might jump back in here and offer some advice.

[Erwin]

@kcantor

Thanks for your input! This brought me a little bit closer to the solution. I did what you suggested and created the following:

Measure1 = CALCULATE(SUM('Table'[Amount]),FILTER(ALL('Calendar'[Date]),'Calendar'[Date]<=MAX('Calendar'[Date])))

Measure2 = CALCULATE([Measure1],'Table'[Doc type]="invoice",'Table'[Doc date]<=DATE(2017,3,5),OR('Table'[Clearing date]>=DATE(2017,3,5),ISBLANK('Table'[Clearing date])=TRUE()))

The question that I have left is how to make the key date dynamic. In other words, how do I get DAX to give me the correct open amount for each date in the Calendar table?

Rg. Erwin

[kcantor]

@ErwinYou may need to look at using SUMX in your first calculation to pull it up at the table level.

[Erwin]

@kcantor

I don't understand your reference to SUMX. There is no row-by-row calculation needed, just application of multiple filters. Can you comment on this?

Anyway, I tried to work around the key date issue by referencing Calendar. Pls review the Measure2 formula below.

Measure1 = CALCULATE(SUM('Table'[Amount]),FILTER(ALL('Calendar'[Date]),'Calendar'[Date]<=MAX('Calendar'[Date])))

Measure2 = CALCULATE([Measure1],'Table'[Doc type]="invoice",'Table'[Doc date]<=ALL('Calendar'[Date]),or('Table'[Clearing date]>=ALL('Calendar'[Date]),ISBLANK('Table'[Clearing date])=TRUE()))

In a way, this works. As long as no date limitation is applied to the visual Measure2 will give the correct amount for each date. The problem is that not all visuals will work (e.g. table and matrix will give error message "A table of multiple values was supplied where a single value was expected"). I suspect this is due to the fact that all dates in Calendar are referenced in the formula.

So, one step closer to a solution, but not yet exactly what I want. Can you help me progress on this?

Rg. Erwin

[Erwin · Accepted solution]

I got a solution! This is it:

Measure1 = CALCULATE(SUM('Table'[Amount]),FILTER(ALL('Calendar'[Date]),'Calendar'[Date]<=MAX('Calendar'[Date])))

Measure2 = CALCULATE([Measure1],'Table'[Doc type]="invoice",'Table'[Doc date]<=VALUES('Calendar'[Date]),or('Table'[Clearing date]>=VALUES('Calendar'[Date]),ISBLANK('Table'[Clearing date])=TRUE()))

Measure2 will respond both to a single date value (open items per key date), as well as show values in a graph visual.

Thanks for your input, it's appreciated.

Rg. Erwin