cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
springje
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
Super User IV
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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

9 REPLIES 9
Super User IV
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
https://www.linkedin.com/in/excelenthusiasts/
springje
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.

Super User IV
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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

springje
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.

dimage.png

Super User IV
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
https://www.linkedin.com/in/excelenthusiasts/
Highlighted
springje
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

image.png

 

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

image.png

Super User IV
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
https://www.linkedin.com/in/excelenthusiasts/
springje
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
Super User IV

Re: Compare milestone dates of snapshot data in same table

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
New Ranks Launched March 24th!

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!

‘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

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.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors