Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
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)
User | Count |
---|---|
42 | |
28 | |
24 | |
20 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |