topic Re: calculate days count between 2 dates in DAX Commands and Tips

calculate days count between 2 dates

Wadda7AboUdai — Thu, 04 Apr 2024 20:40:21 GMT

Hello Everyone

I want to calculate days starts with item purchese date to item transfer date , items can be more than ones in the table it means can be purchesd more than ones and for all branches , I want to calculate the days for each branch and sorted by dates if there are more than one purches order

Here is the source table

item	transaction number	type	date	decs	branch
A	01	3	01-01-2024	purchase order	A
A	01	22	10-01-2024	Transfer order	A
A	02	3	07-01-2024	purchase order	A
A	02	22	14-01-2024	transfer	A
B	03	3	01-01-2024	purchase	A
B	04	3	01-01-2024	purchase	B
B	03	22	13-01-2024	transfer	B

I want to Get the result like this

item	transaction number	branch	Days Count
A	01	A	9
A	02	A	7
B	04	B	12

Re: calculate days count between 2 dates

lbendlin — Thu, 04 Apr 2024 22:32:08 GMT

what happened to transaction number 03 ?

Re: calculate days count between 2 dates

Wadda7AboUdai — Thu, 04 Apr 2024 22:35:26 GMT

still not transferd to branch

Re: calculate days count between 2 dates

lbendlin — Thu, 04 Apr 2024 22:59:43 GMT

so the last row should read 04?

BTW, where I come from Jan 1 to Jan 13 is 13 days, not 12 days.

Re: calculate days count between 2 dates

Anonymous — Fri, 05 Apr 2024 08:00:22 GMT

Hi Wadda7AboUdai ,

lbendlin , thanks for your attention to this situation. I tried to modify the data myself and implement the result according to the user's request. Please check if it can be improved. Here is my solution:

1. create a calculated column to get the number of days between intervals.

Days Count = VAR PurchaseDate = CALCULATE(MIN('Table'[date]), 'Table'[decs] = "purchase", ALLEXCEPT('Table', 'Table'[item], 'Table'[branch],'Table'[transaction number])) VAR TransferDate = CALCULATE(MAX('Table'[date]), 'Table'[decs] = "transfer", ALLEXCEPT('Table', 'Table'[item], 'Table'[branch],'Table'[transaction number])) RETURN DATEDIFF(PurchaseDate, TransferDate, DAY)

2. Create a calculation table to get the desired result.

Summary Table = var _table= SUMMARIZE( 'Table', 'Table'[item], 'Table'[transaction number], 'Table'[branch], 'Table'[Days Count] ) return FILTER( _table,[Days Count]<>BLANK())

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: calculate days count between 2 dates

Wadda7AboUdai — Fri, 05 Apr 2024 19:22:31 GMT

Hi Anonymous
thank you for your help
the table shows nothing , I think I did some thing wrong

the categoryreferance 3= purchse , 22 = transfer

Re: calculate days count between 2 dates

tamerj1 — Fri, 05 Apr 2024 21:27:59 GMT

Hi Wadda7AboUdai

this can be a measure. Place [Item], [TransactionNumber] & [Branch] in a table visual along with the following measure:

Days Count =
VAR CrrentTransactions = 'inventtransn'
VAR PurchaseDate =
CALCULATE (
MIN ( ' inventtransn'[DATEPHYSICAL] ),
'inventtransn'[REFERENCECATEGORY] = 3
)
VAR TransferDate =
CALCULATE (
MAX ( ' inventtransn'[DATEPHYSICAL] ),
'inventtransn'[REFERENCECATEGORY] = 22
)
RETURN
IF (
NOT ISBLANK ( TransferDate ),
DATEDIFF ( PurchaseDate, TransferDate, DAY )
)

Re: calculate days count between 2 dates

Wadda7AboUdai — Sat, 06 Apr 2024 19:21:38 GMT

tamerj1
Thank you sp much for your help this worked fine with me when the item comes one time , when item comes more than one time I want to calculate days between REFERENCECATEGORY 3 and the next REFERENCECATEGORY 22 it means first find the first purchase date and go to next transdate not to last trans date , in the below picture it should calculate 3 time for the same item

I want to show me the item 3 times each time how many day different

Re: calculate days count between 2 dates

tamerj1 — Sun, 07 Apr 2024 07:59:10 GMT

Wadda7AboUdai
Yes that is because in expected result that you have presented, the transaction number is part of the filter context. If you place the transaction number in the result summary table it should work. However, the following should work in both senarios.

Days Count = SUMX ( SUMMARIZE ( 'inventtransn', 'inventtransn'[item], 'inventtransn'[transaction number] ), VAR PurchaseDate = CALCULATE ( MIN ( 'inventtransn'[DATEPHYSICAL] ), 'inventtransn'[REFERENCECATEGORY] = 3 ) VAR TransferDate = CALCULATE ( MAX ( 'inventtransn'[DATEPHYSICAL] ), 'inventtransn'[REFERENCECATEGORY] = 22 ) RETURN IF ( NOT ISBLANK ( TransferDate ), DATEDIFF ( PurchaseDate, TransferDate, DAY ) ) )

Re: calculate days count between 2 dates

AnalyticsWizard — Sun, 07 Apr 2024 08:53:48 GMT

Wadda7AboUdai

To calculate the number of days between the purchase date and the transfer date for each item in Power BI, you can create a new column using the DATEDIFF function 1 2. Here’s an example of how you might write this:

Days Count = 
CALCULATE(
    DATEDIFF(
        MINX(FILTER(Table1, Table1[type] = 3), Table1[date]), 
        MAXX(FILTER(Table1, Table1[type] = 22), Table1[date]), 
        DAY
    ),
    ALLEXCEPT(Table1, Table1[item], Table1[transaction number], Table1[branch])
)

In this formula, Table1 is the name of your table. This formula calculates the number of days between the minimum date (purchase date) and the maximum date (transfer date) for each combination of item, transaction number, and branch. The ALLEXCEPT function is used to remove any filters that might limit the rows being evaluated, except for the filters on the item, transaction number, and branch columns 1 2.

Please replace Table1 with your actual table name. If you need further assistance or have more specific requirements, feel free to ask! 😊

Re: calculate days count between 2 dates

Wadda7AboUdai — Sun, 07 Apr 2024 19:32:56 GMT

Hi tamerj1

After applied this measure it shows balnk in result table

Re: calculate days count between 2 dates

Wadda7AboUdai — Sun, 07 Apr 2024 19:48:57 GMT

Hi AnalyticsWizard

Thank you for your Replay , I want to inform you that the transaction number is different between purchase and transfer order

the purchase order transaction number can be (scr-10023)

the transfer number can by (scr-2324)

and so on there is no related betwwen them

Re: calculate days count between 2 dates

tamerj1 — Sun, 07 Apr 2024 20:28:07 GMT

Wadda7AboUdai

Can you please add the [VOUCHER] column to the detailed table visual and share a screenshot?

Re: calculate days count between 2 dates

Wadda7AboUdai — Sun, 07 Apr 2024 23:11:34 GMT

showed Blank

Re: calculate days count between 2 dates

tamerj1 — Mon, 08 Apr 2024 12:14:26 GMT

Wadda7AboUdai

I meant in the detailed table not the in tge summary table.

Re: calculate days count between 2 dates

Wadda7AboUdai — Mon, 08 Apr 2024 18:52:23 GMT

Re: calculate days count between 2 dates

tamerj1 — Mon, 08 Apr 2024 19:32:34 GMT

Wadda7AboUdai

In the sample data presented in your original post, there was a column called [Transaction Number] that identifies every purchase cycle of each item. In my dax I have used that column which you have replaced, for some reason, with [Voutcher] column.
please calrify this point.

Re: calculate days count between 2 dates

Wadda7AboUdai — Mon, 08 Apr 2024 19:40:28 GMT

Hi tamerj1

I named that transaction number just to make it more simple to explain the table content
Voutcher is the same thing has uniqe number for each transaction ,I apologize if I did not explain this more simply

Re: calculate days count between 2 dates

tamerj1 — Mon, 08 Apr 2024 20:24:31 GMT

Wadda7AboUdai

Except it is not the same thing. Voucher defines each single transaction. What is required is a column that groups the transactions that belong to each cycle. If such column does not exist then it has to be created. If not coming from the source then I would say that won't be an easy job.

Re: calculate days count between 2 dates

Wadda7AboUdai — Mon, 08 Apr 2024 20:23:00 GMT

tamerj1 Yes, that is correct. Could the method be to search for the first purchase transaction and then calculate the date Different to transfer order closest to the purchase date?