Forum Discussion

kballar1's avatar
kballar1
Helper I
2 years ago
Solved

SUMX Help

 

I created a measure using SUMX to get the number of days between two dates so that I could see how many days it takes an account manager to approve something once it's submitted for approval. However now I am seeing issues with it. Some of the results "Lag" are correct but some say "112" lag but the dates are not 112 days in between. I used: 

 

Measure = SUMX(Table1, DATEDIFF(Table1 [Physical Date],Table1 [PriceApproved.date], DAY))

 

DAX SUMX
“DAX"
  • PijushRoy's avatar
    PijushRoy
    2 years ago

    Hi kballar1 

    In your data screenshot, the Physical Date and Price Approved Date are not in Date format. Please make both dates as Date format.
    Use Power Query to correct your data datatype


    Then use the measure

    Measure = DATEDIFF(SELECTEDVALUE('Table (2)'[Physical Date]),SELECTEDVALUE('Table (2)'[Price Approved]),DAY)

     

     

     



    You will find all date diff are correct

    If solved your requirement, please mark this answer as SOLUTION.

    Thanks
    Pijush

7 Replies

    • kballar1's avatar
      kballar1
      Helper I
      2 years ago

       Hi, that didn't work, here is the error message 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi kballar1 ,

    Your measure is working fine with my test data. I recreated a measure for you  to test.

    Here some steps that I want to share, you can check them if they suitable for your requirement.

    Here is my test data:

    1.Create a measure

    Lag = SUMX(
    'Table', 
    INT('Table'[Price Approved] - 'Table'[Physical Date])
)

    2.Final output

     

    Best Regards,
    Albert He

     

    • kballar1's avatar
      kballar1
      Helper I
      2 years ago

       Hi,

      I tried that but as you can see, it works one some but not the other. There are not 48 days between 12/21/2023-1/02/2024. How do I fix this? 

      • PijushRoy's avatar
        PijushRoy
        Community Champion
        2 years ago

        Hi kballar1 

        In your data screenshot, the Physical Date and Price Approved Date are not in Date format. Please make both dates as Date format.
        Use Power Query to correct your data datatype


        Then use the measure

        Measure = DATEDIFF(SELECTEDVALUE('Table (2)'[Physical Date]),SELECTEDVALUE('Table (2)'[Price Approved]),DAY)

         

         

         



        You will find all date diff are correct

        If solved your requirement, please mark this answer as SOLUTION.

        Thanks
        Pijush

  • Dangar332's avatar
    Dangar332
    Resident Rockstar
    2 years ago

    try below measure 

     

     

    measure =
  datediff(
     min('tablename'[physiacl date]),
     min('tablename'[price approved]),
     day
  )