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
211PVM112
New Member

Price Volume Effect with customizable timeperiod, and dynamic with rest of dataset

Hi,

Hope there are anyone who could/would like to help me with a question I have related to PVM analysis with customizable time period, and dynamic with the rest of the dataset at the same time.

I have a whole dataset with different business-related data (customer, product, order), all connecting to one sales table through relations and separate DimKeys.

What I would like to do is to create a price volume mix measure which calculates on a product level. I have seen many solutions to do this by using VAR and order. But the problem is that all of the once I have seen relates to Current Years vs Previous Year. I would like to create a model which allow me to use any two periods I would like. For example, the PVM analysis on a product level for year 2016 vs year 2020 (not 2016 to 2020) or March 2018 vs. June 2019 and so on.

On other words:
A PVM analysis on a product level with any two different time baselines, which is connected to the rest of that data set and changes dynamically according to filters such as customers or product segment. I would like to make the end user be able to choose any start and end period for the PVM calculation, directly in the Dashboard. Is there a way to do so? Or is there a constraint on the time period which makes it difficult?

Thank you very much.

1 REPLY 1
Greg_Deckler
Super User
Super User

 Well, it sounds like what you need are 2 disconnected tables with your years listed. Then, you could do something below. Obviously, this is just a sample on how to compare to different years.

Measure = 
  VAR __Year1 = SELECTEDVALUE('YearsTable1'[Year])
  VAR __Year2 = SELECTEDVALUE('YearsTable2'[Year])
  VAR __Table = FILTER(ALLSELECTED('Table')
  VAR __Table1 = FILTER(__Table,YEAR([Date]) = __Year1)
  VAR __Table2 = FILTER(__Table,YEAR([Date]) = __Year2)
RETURN
  SUMX(__Table1) - SUMX(__Table2)

@211PVM112


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.

Top Solution Authors