SUMX and Time Intelligence

[Anonymous]

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

[Anonymous · Accepted solution]

Solved with this answer! Thanks all!

https://community.powerbi.com/t5/Desktop/Sum-of-values-in-a-measure-with-divide-measure/m-p/296768#M...

[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

https://youtu.be/OBf0rjpp5Hw

https://youtu.be/j0ug-XQgZtg

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

[parry2k]

@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/38490 

[Anonymous]

@parry2k 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]))))

[Anonymous]

@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