Struggling to write simple DAX year over year inflation calculation. HELP/RESOURCES APPRICATIED!

[Anonymous]

I know that this might be a duplicate question but I have failed to find a solution to my problem after about 5 hours of searching. Sorry in advance if it is.

The data

I have three tables that are in the DIrectQuery Storage Mode. One is called LINE_ITEM it has a one to many relationship with another table INVOICE. The INVOICE table has a one to many relationship with another table CONTRACT. Each CONTRACT has one INVOICE per year. Each INVOICE has one or more line items, all of which have a N_PAID_AMOUNT.

Goal

I'm looking to calculate the percent increase in the ammount paid each INVOICE year.

Limitations

This same pbix file will be used to do many other things and needs to be very responsive. I do not want to manipulate the queries or relationships in any way if possible for the sake of both speed and simplicity. I would like to solve this with a measured or calculated column.

Where I'm stuck at

I have a formula, which I am using in a measure

Previous Paid Amount = CALCULATE(SUM(LINE_ITEM[N_PAID_AMOUNT]), YEAR(INVOICE[DT_START]) = YEAR(INVOICE[DT_START])-1)

It throws an error "Cannot find name '[DT_START]'." only for the second occurance in the filter expression. If I replace the formula with,

Previous Paid Amount = CALCULATE(SUM(LINE_ITEM[N_PAID_AMOUNT]), YEAR(INVOICE[DT_START]) = NOW())

It does not throw errors but obviously does not help me calculate what I need.

Other things I've tried

I have tried using the EARLIER DAX function, I have tried changing the data to import mode, I have tried numerous other formulas and functions. I have tried all kinds of quick measures including the one that is supposed to do this exact calculation and none have been successful.

Let me know if theres any other information I can share to help you answer my question.

[Anonymous]

EARLIER is a nasty one, it's much better to use a var, easier to understand.
Also, EARLIER is generally useful in a calculated column rather than a measure, but it looks to me that it's not what you're looking to obtain.
Also, do not confuse SUM and SUMX. 

But is this your model? Can you post a screenshot?

[LINE ITEM] 1----* [INVOICE] * ----1 [CONTRACT]
?

[Anonymous]

Thank you for such a quick response! Yes that is my model. I am not confident in my understanding of sum and sumx (and similar functions with x) but I do believe that I want to use sum.

[Anonymous]

I solved the problem with the following function.

Previous Paid Amount = CALCULATE(SUM(LINE_ITEM[N_PAID_AMOUNT]),INVOICE[Year]-1)

Nomatter what I could not use the following formula.

Previous Paid Amount = CALCULATE(SUM(LINE_ITEM[N_PAID_AMOUNT]),YEAR(INVOICE[DT_START])-1)

The [Year] column was created by a calculated column with the following formula.

Year = YEAR(INVOICE[DT_START])

Thank you everyone who helped. Anyone who knows why this is please share! I can provide more information as needed. Will accept any answer that explains this.

[Anonymous]

Correction... This did not solve the problem. It only appeared to because it didn't throw errors.

[PedroReis]

Hi @Anonymous , check the sample PBIx with the solution I proposed here, it seems to be working.

Maybe there is something particular in your model that makes it different? If so, please send me back the file with the modifications and I'll adjust it.

[Anonymous · Accepted solution]

@PedroReis  @Anonymous In my model there is exactly one invoice for each year. The date stored for a line item is not relavent at all. The only value that is important in the Line Item Table is the Amount Paid. Invoices that are related to eachother will share a relationship with the same contract. The part of my formula that I am struggling to write is finding the value of a contract's last year paid amount. I cannot upload the file at the moment however I did write a formula solves my problem. I assume that this is over complicated, is there a way I could write this formula simpler? (I do not want to use a date/calendar table)

The following is the formula for a measured column in the Invoice Table

Paid Amount = CALCULATE(SUM(LINE_ITEM[N_PAID_AMOUNT]))

The following is the formula that solves my problem. Finds the last year's paid amount.

Paid Amount LY = 
CALCULATE(
	INVOICE[Paid Amount],
	FILTER(
		ALLSELECTED('INVOICE'[Year]),
		ISONORAFTER('INVOICE'[Year], MAX('INVOICE'[Year])-1, DESC)
	)
) -
CALCULATE(
	INVOICE[Paid Amount],
	FILTER(
		ALLSELECTED('INVOICE'[Year]),
		ISONORAFTER('INVOICE'[Year], MAX('INVOICE'[Year])-2, DESC)
	)
)

[PedroReis]

If this is your model:

[CONTRACT] 1---* [INVOICE] 1---* [LINE_ITEM]

And if you are summing up your measures based on the Invoice table:

- Use RELATEDTABLE for refencing Line_Item Columns

- Use RELATED for referencing Contract Columns

I'm confused as in the formula you presented you use the Year from Invoice, but you said you wanted to compare by the Contract Year?

[Anonymous]

Hi, have you solved? @PedroReis solution looked in the right direction. 
And by the way,

SUM is a function that sums the values, for example in a column. Simple, straightforward, will sum all the values.

SUMX is a function (like all the X ones) that *iterates* row by row and makes "things" on your data and then SUM at the end the values.

For example if you want to summarize the values of the sales amount  but only for certain products, you can't do it with SUM as will simply sum everything. SUMX on the contrary, accepts a filter so you can SUMX(table with filter, your value). Also SUMX accepts formula in the summatory, so if you want to sum Amount*Number of Products you can't with SUM as SUM accepts only a column, while in SUMX you can write SUMX(table, [amount]*[Number of])

[Anonymous]

Correction. No, my model is as follows.

[CONTRACT] 1---* [INVOICE] 1---* [LINE_ITEM]

[PedroReis]

Hi @Anonymous 

I suggest that you externalize the date to a Date Dimension, and create there the year column. This will ease a lot the calculation.

You can do it automatically with all the dates in the DT_START Column:

Line Item Calender = CALENDAR(FIRSTDATE(LINE_ITEM[DT_Start]);LASTDATE(LINE_ITEM[DT_Start]))

Then you can create the measures:

Paid Amount = sum(LINE_ITEM[N_PAID_AMOUNT])

Paid Amount LY = CALCULATE([Paid Amount];DATEADD('Line Item Calender'[Date];-1;year))

Paid Amount Var = DIVIDE([Paid Amount];[Paid Amount LY];0)

Or you can create a KPI just for the last Year:

Paid Amount Var Current Year = 
VAR PaidAmountCurrentYear = CALCULATE([Paid Amount];'Line Item Calender'[Year]=year(NOW())-1)
VAR PaidAmountPreviousYear = CALCULATE([Paid Amount];'Line Item Calender'[Year]=year(NOW())-2)
Return
divide(PaidAmountCurrentYear;PaidAmountPreviousYear;0)

[Anonymous]

I appriciate your reply! However, it did not work. I got a data oleAut date error. https://pastebin.com/pJ7yfD6R