Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi there. I am a total Power BI newbie having just taken a Power BI intro class. I do have extensive query/SQL and programming experience, though.
I am trying to create a visualization that allows me to compare variances in snapshots of data. I have a database stored procedure that runs weekly to take a snapshot of the last 12 weeks of daily production volumes data. The daily volumes data for the same day can change over the course of time and folks want to analyze the greatest positive/negative variances between 2 snapshots.
Here is a simplistic example of my data:
- 3 snapshots of production from 11/1/17 to 11/4/17 for 2 different wells
Snapshot_date Production_date Production_volume Well
12/17/17 11/1/17 100 A
12/17/17 11/2/17 101 A
12/17/17 11/3/17 100 A
12/17/17 11/4/17 105 A
12/17/17 11/1/17 40 B
12/17/17 11/2/17 44 B
12/17/17 11/3/17 48 B
12/17/17 11/4/17 42 B
12/24/17 11/1/17 100 A
12/24/17 11/2/17 110 A
12/24/17 11/3/17 120 A
12/24/17 11/4/17 130 A
12/24/17 11/1/17 40 B
12/24/17 11/2/17 44 B
12/24/17 11/3/17 48 B
12/24/17 11/4/17 42 B
12/31/17 11/1/17 100 A
12/31/17 11/2/17 110 A
12/31/17 11/3/17 120 A
12/31/17 11/4/17 130 A
12/31/17 11/1/17 50 B
12/31/17 11/2/17 44 B
12/31/17 11/3/17 48 B
12/31/17 11/4/17 49 B
I want to allow a user to choose 2 different snapshot_dates to compare and choose the production_date range they want to compare. So let's say I want to compare any variance between the 12/17/17 snapshot and 12/31/17 snapshot for 11/1-11/2. I have a time slicer for the production_date that allows me to choose 11/1 to 11/2. I have a time slicer to choose my snapshot_date in listbox format. But I cannot figure out how to allow the user to choose another snapshot_date to compare to and run variance calculations.
Thanks in advance to anyone who can help me!
Solved! Go to Solution.
Hi @evonwu,
Maybe below formula will suitable for your requirement:
AVG by Product = CALCULATE(AVERAGE([Production_volume]),VALUES('sample'[Well]),FILTER(ALLSELECTED('sample'),[Well]=MAX([Well])&&[Production_date]=MAX([Production_date])))
Notice: I use average mode to summary these values, one of value field affect by snap date and product date, another only affected by product date.
Regards,
Xiaoxin Sheng
Hi @evonwu,
Maybe below formula will suitable for your requirement:
AVG by Product = CALCULATE(AVERAGE([Production_volume]),VALUES('sample'[Well]),FILTER(ALLSELECTED('sample'),[Well]=MAX([Well])&&[Production_date]=MAX([Production_date])))
Notice: I use average mode to summary these values, one of value field affect by snap date and product date, another only affected by product date.
Regards,
Xiaoxin Sheng
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
101 | |
75 | |
63 | |
62 |
User | Count |
---|---|
141 | |
104 | |
101 | |
80 | |
67 |