Month over Month Price Change (with last available price)

[pawelj795]

Hi everyone, I have a unit price table as follows:

I need to calculate month to month price movements (%).

I have 3 main product groups with a few subgroups.

Column sections present last year months.

The main issue for me is that some products weren't sell in 2 consecutive months.

Another possible problem could be that in one month could be a few sales of the same item.
That means I need average unit price on particular month.

I tried with various posted solutions but nothing have been seem working for me.

Thank you in advance for help!

[pawelj795]

It looks like it's gonna work!

One question though, how to hide infinity value in first month?

[v-eachen-msft]

Hi @pawelj795 ,

You could edit the DAX with an IF function.

IF(a<>BLANK(),
('Table (2)'[value]-a)/a,BLANK())

[pawelj795]

Turns out, its not working properly.

Maybe because I have various values for same month?
I mean in same month, one product could be sold for different prices -> that's why I want to calculate average price in every month

[pawelj795]

[pawelj795]

up

[v-eachen-msft]

Hi @pawelj795 ,

You could use SUMMARIZECOLUMNS() function to create a table with average value per month( group by month ).

[pawelj795]

Ok, I tried that solution but how modify your previous functions (rank & value%) to work with this new table?

I mean it's working only when I had one distinct value per month.
I cant really solve how to get deeper and have grouped values by group also, which mean I would have 3 values per every month.

[pawelj795]

Ok, I tried that solution but how modify your previous functions (rank & value%) to work with this new table?

I mean it's working only when I had one distinct value per month.
I cant really solve how to get deeper and have grouped values by group also, which mean I would have 3 values per every month.

[pawelj795]

up

[pawelj795]

up

[pawelj795]

up

[pawelj795]

up

[pawelj795]

Turns out, it's not working properly.

Maybe because I have various values for the same month

[v-eachen-msft]

Hi @pawelj795 ,

You could ignore blank value with functions.

Here is my test file for your reference.