Reply
Member
Posts: 70
Registered: ‎04-05-2017
Accepted Solution

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

 


Accepted Solutions
Highlighted
Established Member
Posts: 200
Registered: ‎07-12-2017

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

 

View solution in original post


All Replies
AlB Super Contributor
Super Contributor
Posts: 879
Registered: ‎11-12-2018

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
Established Member
Posts: 200
Registered: ‎07-12-2017

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

 

AlB Super Contributor
Super Contributor
Posts: 879
Registered: ‎11-12-2018

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
)

 

Member
Posts: 70
Registered: ‎04-05-2017

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.

AlB Super Contributor
Super Contributor
Posts: 879
Registered: ‎11-12-2018

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

@mayank08

I don't exactly understand what you want to do

Member
Posts: 70
Registered: ‎04-05-2017

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.