cancel
Showing results for 
Search instead for 
Did you mean: 
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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors