Count different days from date on the record before

[jppuam]

Hello, i'm trying to count the days between the record date and the previous record (as i show in the example)

For each Numbef, i've a appointment date, and i've to calculate the diference between the record date, and the previous record.

For example, in Numbef = 3, 146 days (on one record) that is the diference between 18 July and 22 Fev.

Can you help me with this ?

thanks,

jppuam

[amitchandak]

@jppuam ,

As a new colum

Diff COlumn = datediff(maxx(filter(Table,Table[Date]<earlier(Table[Date]) && Table[Numberf]= earlier(Table[Numberf]) ),Table[Date]) ,Table[Date],Day)

As a measure

example with date table

Last Day Non Continous = CALCULATE(sum('order'[Qty]),filter(all('Date'),'Date'[Date] =MAXX(FILTER(all('Date'),'Date'[Date]<max('Date'[Date])),'order'['Date'])))
Day behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Day))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[jppuam]

Hi amitchandak,

thanks for the response, but i quite not compreend.

can i sent by email you a test file for you to exemplified ?

NumBeneficiario	DataFimServico	NumPrestador	Dias entre AtosMedicos
1	02/01/2019	374898	0
2	23/02/2019	374898	0
2	19/12/2019	369757	299
2	20/12/2019	374898	1
3	22/02/2019	374914	0
3	18/07/2019	364971	146
3	22/11/2019	374914	127
4	12/07/2019	373376	0
4	19/07/2019	374914	7
4	20/08/2019	373376	32
4	24/09/2019	373376	35
4	16/10/2019	356157	22
4	10/12/2019	373376	55
5	22/02/2019	374914	0
5	08/04/2019	367197	45
5	18/07/2019	364971	101
6	11/03/2019	372348	0
6	27/05/2019	372348	77
6	16/07/2019	372348	50
6	26/07/2019	372348	10
6	30/07/2019	372348	4
6	02/10/2019	372348	64
6	03/12/2019	372348	62
6	18/12/2019	372348	15
7	04/03/2019	372348	0
7	13/05/2019	355018	70
7	08/08/2019	355018	87

thanks very much,

jppuam

[harshnathani · Accepted solution]

Hi @jppuam ,

Click on Calculated Column

Difference in Days =

var _a = CALCULATE(MAX('Table'[DataFimServico]),Filter('Table','Table'[NumBeneficiario]=EARLIER('Table'[NumBeneficiario]) && 'Table'[DataFimServico] < EARLIER('Table'[DataFimServico])))

var _b = DATEDIFF(_a,'Table'[DataFimServico],DAY)

RETURN

IF (_b =BLANK(),0,_b)

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[jppuam]

Thank you harshnathani it works perfect.

i've another question regarding this issue, but i'll create another post.

regards,

jppuam

[harshnathani]

Hi @jppuam ,

Please see this link

https://community.powerbi.com/t5/Community-Blog/Getting-Previous-Values-in-Power-BI-Part-1/ba-p/1143...

Regards,

Harsh Nathani