Forum Discussion
SUMX and Time Intelligence
Hello,
Thanks for reading.
I am trying to create a measure utilizing SUMX. I want to calculate at the row level a Ratio * Dollar amount and sum it up.
The data is in a similar format as to what is below but due to company policy I dont believe I can post real data.
| Date | Product | Sold | Total Dollars Sold |
| 201901 | A | 10 | 1000 |
| 201901 | B | 16 | 1200 |
| 202001 | A | 12 | 1400 |
| 202001 | B | 16 | 1200 |
The final measure is something like
FinalMeasure = SUMX(SalesTable, [PriceChange] * [Total Dollars Sold])
I created a price measure--
Price = DIVIDE(SUM(Total Dollars Sold), SUM(Sold))
Then creating a Prior Year Price--
PYPRICE = CALCULATE([Price], SAMEPERIODLASTYEAR('YearMonthBridge'[Date]))
Then a Price Change measure by taking the quotient of the above two measures.
While troubleshooting my SUMX measure i began to SUMX each portion of the measure to determine where the issue was. When I did a SUMX of the PY measure--
SUMXPY = SUMX(SalesTable,PYPRICE)
The returned value is always nothing/null. All of the other SUMX(PRICE) etc... are working correctly.
Edit-- I should also add. The PY Price measure works correctly by itself as my Date column is connected to a Date Table.
Is it possible to use SUMX with Time Intelligence? I am having a hard time wrapping my head around this!! Any help would be appreciated.
Thanks for reading
Solved with this answer! Thanks all!
5 Replies
- parry2k
Super User
Anonymous seems like you are making it super complicated. What is your end goal?
Read this post to get your answer quickly.
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490parry2k
Apologies, I believe I solved it this morning so I should be able to encapsulate the problem.This measure below returns null:
PriceChange = SUMX(Sales Table, Divide([Price], CALCULATE([Price], SAMEPERIODLASTYEAR(YearMonthBridge[Date]))))parry2k I apologize for the confusion-- please see below-
PriceChange = Var Pricechange = Divide([Price], CALCULATE([Price], SAMEPERIODLASTYEAR(YearMonthBridge[Date]))) return SUMX(Sales Table, Pricechange)Is producing the wrong results.
Should the below measure work or produce Null? Now that I think about it the data from the SAMEPERIODLASTYEAR() is not on the same row as current year? Would the solution be to create a column with prior year data on current year rows?
PriceChange = SUMX(Sales Table, Divide([Price], CALCULATE([Price], SAMEPERIODLASTYEAR(YearMonthBridge[Date]))))If this is still confusing maybe I can create a sample .pbix with fake data
- amitchandak
Anonymous , Not very clear. But you should use a date table for time intelligence
example
PYPRICE = CALCULATE([Price], SAMEPERIODLASTYEAR('Date'[Date]))PYPRICE = CALCULATE([Price], dateadd('Date'[Date],-1,year))
Refer to my video on why time intelligence can fail
Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA
Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA Solved with this answer! Thanks all!
