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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Cristina101083
Frequent Visitor

Price % change over dynamic starting period

I have a bar chart which is giving me the trend of pricing for a specific period which I am manipulating with a slicer.

What I need is the variance month over month vs. that first bar displayed.

 

Example:

 

January 1250

February 1300

March 1500

April 1200

May 1500

June 1150

 

January is always my fixed concept

So, I want to chart 

 

February 4%

March 20%

April -4%

May 20%

June -8%

 

How can I do this calculation?

Keep in mind that if the month in the chart is changed based on the slicer, it needs to recalculate that beginning value.

 

3 REPLIES 3
v-huizhn-msft
Employee
Employee

Hi @Cristina101083,

I try to reproduce using your sample data table.

1.PNG

 

Percentage = ( MAX(Table3[value])-CALCULATE(MAX(Table3[value]),Table3[Month]="Jan"))/CALCULATE(MAX(Table3[value]),Table3[Month]="Jan")


Then create a slicer, and a table visual to display the result.

2.PNG4.PNG

If you have other issues, please feel free to ask.


Best Regards,

Angelia


Angelia, 

 

Thanks for your reply, unfortunately I need the month to be dynamic based on the slicer selection. 

I was able to do a similar formula which is:

 

January Price=calculate([PipeLogixPrice],filter(all('Calendar'[Date]),and('Calendar'[Date]>=date(2016,1,1),'Calendar'[Date]<=date(2016,1,31))))

This formula gives me the January Price. Then I divide each month's value vs. that January Price.

 

Unfortunately, what I really need, is that January Price(which is now static) needs to be dynamic based on the selection of the slicer.

Thanks!

 

Hi @Cristina101083,

Please create a measure to get the selected date in slicer.

selected=CALCULATE(MAX(Table[Date]).ALLSELECTED(Table))

Then use the measure in your formula.

Best Regards,
Angelia

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.