topic Re: how to calculate growth as column with categories in DAX Commands and Tips

how to calculate growth as column with categories

nada — Wed, 05 Jan 2022 06:13:52 GMT

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

Re: how to calculate growth as column with categories

VahidDM — Wed, 05 Jan 2022 06:35:47 GMT

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/

Re: how to calculate growth as column with categories

nada — Wed, 05 Jan 2022 06:49:34 GMT

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

Re: how to calculate growth as column with categories

amitchandak — Wed, 05 Jan 2022 07:01:52 GMT

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-bd52912a5bd4

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.

Re: how to calculate growth as column with categories

nada — Wed, 05 Jan 2022 07:23:42 GMT

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

Re: how to calculate growth as column with categories

amitchandak — Wed, 05 Jan 2022 07:33:52 GMT

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

Re: how to calculate growth as column with categories

nada — Wed, 05 Jan 2022 12:45:27 GMT

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