Forum Discussion

Wadda7AboUdai's avatar
Wadda7AboUdai
Icon for Helper I rankHelper I
2 years ago

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 ...
Need Hepl
tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
2 years ago

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

Wadda7AboUdai's avatar
Wadda7AboUdai
Icon for Helper I rankHelper I
2 years ago

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 

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    2 years ago

    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 )
        )
)
    • Wadda7AboUdai's avatar
      Wadda7AboUdai
      Icon for Helper I rankHelper I
      2 years ago

      Hi tamerj1 

      After applied this measure it shows balnk in result table 

       

      • tamerj1's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        2 years ago

        Wadda7AboUdai 

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