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
evonwu
New Member

Comparing snapshots of same range of data

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!

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

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])))

1.PNG

 

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

1 REPLY 1
v-shex-msft
Community Support
Community Support

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])))

1.PNG

 

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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.