Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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) ) )
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]
?
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.
I solved the problem with the following function.
Correction... This did not solve the problem. It only appeared to because it didn't throw errors.
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.
@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) ) )
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?
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])
Correction. No, my model is as follows.
[CONTRACT] 1---* [INVOICE] 1---* [LINE_ITEM]
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)
I appriciate your reply! However, it did not work. I got a data oleAut date error. https://pastebin.com/pJ7yfD6R
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |