cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mayank08 Member
Member

Need Difference of Date in term of Day in Table View

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
affan Established Member
Established Member

Re: Need Difference of Date in term of Day in Table View

Hi @mayank08

 

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 diffrence from previous date.pngDate 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

 

6 REPLIES 6
Super User
Super User

Re: Need Difference of Date in term of Day in Table View

Hi @mayank08

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

Highlighted
affan Established Member
Established Member

Re: Need Difference of Date in term of Day in Table View

Hi @mayank08

 

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 diffrence from previous date.pngDate 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

 

Super User
Super User

Re: Need Difference of Date in term of Day in Table View

@mayank08

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
)

 

mayank08 Member
Member

Re: Need Difference of Date in term of Day in Table View

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.

Super User
Super User

Re: Need Difference of Date in term of Day in Table View

@mayank08

I don't exactly understand what you want to do

mayank08 Member
Member

Re: Need Difference of Date in term of Day in Table View

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.