cancel
Showing results for
Did you mean:
Frequent Visitor

## Compare milestone dates of snapshot data in same table

I have a dataset that has multiple snapshots of the same milestone data. Example:

Snapshot | Project | Milestone | Date

1          KSS       Submit      1/1/2019

1          KSS       Approve     11/5/2019

1          SIM       Submit      2/13/2019

1          SIM       Approve     10/28/2019

2          KSS       Submit      1/1/2019

2          KSS       Approve     11/5/2019

2          SIM       Submit      2/14/2019

2          SIM       Approve     12/31/2019

3          KSS       Submit      1/1/2019

3          KSS       Approve     11/12/2019

3          SIM       Submit      2/14/2019

3          SIM       Approve     12/31/2019

I need to be able to slice on 2 Snapshots and a Project and display the following:

(Selected slicers would be snapshots 2 and 3, project KSS)

Snapshot | Submit   | Approve

2          1/1/2019   11/5/2019

3          1/1/2019   11/12/2019

Diff. days 0          7

Is this achievable?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV

## Re: Compare milestone dates of snapshot data in same table

Hi,

Write this measure

=if(HASONEVALUE(Data[Snapshot]),MIN(Data[Date]),FORMAT(MAX(Data[Date])-MIN(Data[Date]),"0"))

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
9 REPLIES 9
Super User IV

## Re: Compare milestone dates of snapshot data in same table

Hi,

What result would you expect if the user selects 3 snapshots.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

## Re: Compare milestone dates of snapshot data in same table

I do not want the user to be able to select 3 snapshots. One slicer for snapshot A and a second slicer for snapshot B, then compare the values for snapshots A and B.

Highlighted
Super User IV

## Re: Compare milestone dates of snapshot data in same table

Hi,

Write this measure

=if(HASONEVALUE(Data[Snapshot]),MIN(Data[Date]),FORMAT(MAX(Data[Date])-MIN(Data[Date]),"0"))

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

## Re: Compare milestone dates of snapshot data in same table

Thank you. I have created the below matrix, at this point I am unsure how to only show the measure total for the total row, and the date for the data rows. I do not want to see the measure in the main rows, nor the date in the total row.

d

Super User IV

## Re: Compare milestone dates of snapshot data in same table

You are welcome.  I do not understand your question at all.  What exactly do you want?

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

## Re: Compare milestone dates of snapshot data in same table

Your example shows just the dates in the rows of the matrix, and the difference between them in the total row with a label of Date Diff

Mine has a date and the difference in the Total row. I'd like mine to look like yours

Super User IV

## Re: Compare milestone dates of snapshot data in same table

Hi,

You may download my PBI file from here.

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

## Re: Compare milestone dates of snapshot data in same table

Thank you! I had a typo in my measure, I was using the wrong field in one spot so the Total ended up correct but the values within the matrix were incorrect.

Super User IV

## Re: Compare milestone dates of snapshot data in same table

You are welcome.

Regards,
Ashish Mathur
http://www.ashishmathur.com

Announcements

#### New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

#### ‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

#### Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors