Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
pawelj795
Post Prodigy
Post Prodigy

Month over Month Price Change (with last available price)

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

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!

14 REPLIES 14
pawelj795
Post Prodigy
Post Prodigy

It looks like it's gonna work!

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

Hi @pawelj795 ,

 

You could edit the DAX with an IF function.

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

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.

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


%change.png

%change_v2.png

up

Hi @pawelj795 ,

 

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

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.

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.

v3.png

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.

up

up

up

up

%change.png

 

Turns out, it's not working properly.

 

Maybe because I have various values for the same month

v-eachen-msft
Community Support
Community Support

Hi @pawelj795 ,

 

You could ignore blank value with functions.

Here is my test file for your reference.

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.