Compare milestone dates of snapshot data in same table

[springje]

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?

[Ashish_Mathur]

Hi,

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

[springje]

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.

[Ashish_Mathur · Accepted solution]

Hi,

Write this measure

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

Hope this helps.

[springje]

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

[Ashish_Mathur]

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

[springje]

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

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[springje]

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.

[Ashish_Mathur]

You are welcome.