Getting DateDiff based on same column

[rjsidek]

Hi everyone,

I have a sample dataset that looks like this,

 and a sample pbi template that looks like this:

And here is what I am trying to achieve:

As you can see in the pbi template, there is a slicer for Project Stage and multiple cards to show different information. The cards that are most relevant to this question is Project Stage, DateDiff Prev Stage, and the Stage slicer. 

For the DateDiff Prev Stage, currently it is showing the DateDiff between Stage 2 and Stage 1. I did that by creating a variable for each of the stages and then simply returning the datediff between the 2 to be displayed in the card.

What I ultimately want to do is make this change dynamic. I want to create a measure that when I choose a stage on the slicer, the measure calculates the datediff between my current selection stage and whatever directly previous stage relative to the current selection is. As of now, I have no idea how I can achieve that. 

Any help is appreciated. Thanks in advance

[danextian]

Hi @rjsidek ,

Please try this:

1.  Add a column in Query Editor that would extract the stage number. Name it Stage Number. You can use Text Before Delimiter or First Characters. Set the data type to whole number.
2.  Create these calculated columns in DAX:

Previous Date =
IF (
    'Table'[Stage Number] > 1,
    CALCULATE (
        MAX ( 'Table'[Date] ),
        ALLEXCEPT ( 'Table', 'Table'[Company] ),
        'Table'[Stage Number]
            = EARLIER ( 'Table'[Stage Number] ) - 1
    )
)

Datediff = 
DATEDIFF ( 'Table'[Previous Date], 'Table'[Date], DAY )

[rjsidek]

Hi @danextian , 

I tried doing it how you suggested but it does not seem to be working. This is what it shows currently:

 I provided the tables visualization to show what the measure currently shows. As you can see, the datediff is just showing blanks, and I am not quite sure why.

These are the codes I used to create the 2 calculated columns

Previous Date = 
IF (
    Table1[Stage Number] > 1,
    CALCULATE (
        MAX ( Table1[Date of Event] ),
        ALLEXCEPT ( Table1, Table1[Company Name] ),
        Table1[Stage Number]
            = EARLIER ( Table1[Stage Number] ) - 1
    )
)

Datediff = 
DATEDIFF ( Table1[Previous Date], Table1[Date of Event], MONTH )

Any ideas how to correct this? 

[dax · Accepted solution]

Hi rjsidek, 

I want to check the logic with you, when you choose stage 1, which previous date of it?Shouls it show blank? When you choose 2, it should compare 1 and 2 . You could refer to my sample for details to see whether it work or not.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[danextian]

Can you please post a screenshot of the data view filtered to the stage and company in your screenshot similar to image below?