Forum Discussion
Helper VCount different days from date on the record before
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
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)RETURNIF (_b =BLANK(),0,_b)Regards,
Harsh NathaniDid I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
5 Replies
- amitchandak
Super User
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-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
Appreciate your Kudos.
jppuamHi 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
