how to calculate growth as column with categories

[nada]

Hello .  I would like to ask whether it is possible to add the monthly and annual change if the data has more than one value for the same month, for example sales 1, sales 2, and sales 3, and 3 citys we have 6 January. How can we add a column and define

the monthly change even though there are 3 months at the same time

year	month	city	date	sales
2019	January	Jeddah	1/1/2019	sale 1	55
2019	January	Jeddah	1/1/2019	sale2	102
2019	January	Jeddah	1/1/2019	sale 3	84
2019	January	Riyadh	1/1/2019	sale 1	96
2019	January	Riyadh	1/1/2019	sale2	93
2019	January	Riyadh	1/1/2019	sale 3	91
2019	February	Jeddah	2/1/2019	sale 1	98
2019	February	Jeddah	2/1/2019	sale2	103
2019	February	Jeddah	2/1/2019	sale 3	94
2019	February	Riyadh	2/1/2019	sale 1	92
2019	February	Riyadh	2/1/2019	sale2	94
2019	February	Riyadh	2/1/2019	sale 3	96

[VahidDM]

Hi @nada 

Can you add Expected output from sample data?

Do you want to calculate the change for each month regardless of city? 

Appreciate your Kudos!!
LinkedIn:www.linkedin.com/in/vahid-dm/

[nada]

When adding the growth formula
Prior = CALCULATE(SUM('table'[Value]),PREVIOUSMONTH('table'[Date]),('table'[sales]),('table'[City]))

It does not work because I have a large number of cities and sales, so the month of January is repeated several times and the formula is not read

[amitchandak]

@nada , You should use a date table and try measure like

Prior = CALCULATE(SUM('table'[Value]),PREVIOUSMONTH('Date'[Date]))

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...

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

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[nada]

Thanks for answering my questions, actually The problem here is that the Power PI did not read the result of the previous month because there are many classifications, is it possible to find a solution that shows (the previous month, for the same city, for the same commodity) Also, it must be a column, not a measure

[amitchandak · Accepted solution]

@nada , You are trying to create a column not measure. You should prefer to measure

sumx(filter(Table, [city] = earlier([city]) &&[commodity] = earlier([commodity]) && eomonth([date],-1) = eomonth(earlier([date]),0)), Table[Value])

[nada]

thank you  amitchandak it's work perfectly 

but i can't transform the tabel to qurey (  to unpivot the column)

is there any way to unpivot the column

thank you for responding