Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Wadda7AboUdai
Helper I
Helper I

calculate days count between 2 dates

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 

 

itemtransaction numbertypedatedecsbranch
A01301-01-2024purchase orderA
A012210-01-2024Transfer orderA
A02307-01-2024purchase orderA
A022214-01-2024transferA
B03301-01-2024purchaseA
B04301-01-2024purchaseB
B032213-01-2024transferB

 

I want to Get the result like this 

itemtransaction numberbranchDays Count
A01A
A02A7
B04B12

 

 

21 REPLIES 21
v-kaiyue-msft
Community Support
Community Support

Hi @Wadda7AboUdai ,

 

Could you please tell me if your problem has been solved?

 

If it is, could you please mark the helpful replies as Answered to close this topic?

 

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.

AnalyticsWizard
Super User
Super User

@Wadda7AboUdai 

To calculate the number of days between the purchase date and the transfer date for each item in Pow...12. 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, exce...12.

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

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 

tamerj1
Super User
Super User

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 )
)

@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 

inventtransnerror.jpg

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

@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 )
        )
)

Hi @tamerj1 

After applied this measure it shows balnk in result table 

Wadda7AboUdai_0-1712518262824.pngWadda7AboUdai_1-1712518359506.png

 

@Wadda7AboUdai 

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

showed Blank 

Wadda7AboUdai_0-1712527452146.png

 

@Wadda7AboUdai 

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

Wadda7AboUdai_0-1712602329114.png

 

@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. 

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

@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. 

@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?

@Wadda7AboUdai 

That won't work. In your original post example (item B, transaction number 3) has to be skipped as it hasn't been transferred to branch. The closest transfer date to this transaction is the one that belong to B-4. To identify that this transaction has not been transferred we need to do more work. 

This is very similar to attendance data where you have multiple punch in and punch out in a given date. It's not that it is not possible but it is a bit complicated and probably requires creating a new calculated table.

Best option is to have this identified from the source or at least calculated easier with SQL. 

v-kaiyue-msft
Community Support
Community Support

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:

vkaiyuemsft_0-1712303873318.png


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())

vkaiyuemsft_1-1712303971821.png

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.

Hi @v-kaiyue-msft 
thank you for your help 
the table shows nothing , I think I did some thing wrong 

 

Wadda7AboUdai_0-1712344772938.png

 

Wadda7AboUdai_1-1712344887051.png

 

the categoryreferance  3= purchse , 22 = transfer 

 

lbendlin
Super User
Super User

what happened to transaction number 03 ?

still not transferd to branch

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Kudoed Authors