cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
springje Frequent Visitor
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
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
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/
springje Frequent Visitor
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
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
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors