Need Difference of Date in term of Day in Table View

[Anonymous]

In a table, I have a "department" column  & also "date of occurrence"  column of an event. When I drag this date to the table view, I need the difference of date from the previous date value. It should also filter by "department" Filter. You can see the required output via below screenshot:

https://www.dropbox.com/s/3vsbwqfgfmzpaln/Output2.png?dl=0

https://www.dropbox.com/s/u14hgr2e4p6q01a/Output1.png?dl=0

Please Guide!

Thanks in advance

[AlB]

@Anonymous

You could use a measure like this. It will only work properly if you have single dates on the first column of your table visual as show  

Date_Diff =
DATEDIFF (
    CALCULATE ( MAX ( Table[Date] ), Table[Date] < EARLIER ( Table[Date] ) ),
    VALUES ( Table[Date] ),
    DAY
)

[affan · Accepted solution]

Hi @Anonymous

Difference = 
var _department=FIRSTNONBLANK(Table1[Department],1)

var _Currentdate=CALCULATE(FIRSTNONBLANK(Table1[Occurance],1))

var _lastdate=CALCULATE(LASTNONBLANK(Table1[Occurance],1),Table1[Occurance]<_Currentdate)
return
IF(ISBLANK(_lastdate),0,VALUE(_Currentdate-_lastdate))

I have acheived the following result from this DAX

Date diff

You can see the pbix file used here

If this helped you, please mark this post as an accepted solution and like to give KUDOS .

Regards,

Affan

[Anonymous]

Thanks, it works fine, what changes do I need to do to apply date filter as well, if we select a range of date, for the first value it actually show the difference from the date which is not included in the date range, you can see in the screenshot below:

https://www.dropbox.com/s/t4no1mux8i58da6/output3.png?dl=0

It should be zero.

Please guide.

[AlB]

@Anonymous

I don't exactly understand what you want to do

[Anonymous]

In screenshot when we filter by department and date range, you can see there is a single date in the table view, there is no previous value of the date, so it must show 0 instead of 4.

[AlB]

Hi @Anonymous

What are you trying to achieve? Is this a middle step for something else?