Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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))
Solved! Go to Solution.
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
try below measure
measure =
datediff(
min('tablename'[physiacl date]),
min('tablename'[price approved]),
day
)
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
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?
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
It was already marked as date I was just presenting the data in a table visual. But I tried the code you suggested and it worked. Thank you!
HI @kballar1
Please try below dax
DATEDIFF = DATEDIFF('Table (2)'[Physical Date],'Table (2)'[Price Approved],DAY)
Please show the expected outcome based on the sample data you provided. Find how to provide sample data
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
If solved your requirement, please mark this answer as SOLUTION.
Thanks
Pijush
Hi, that didn't work, here is the error message
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
54 | |
26 | |
23 | |
14 | |
11 |
User | Count |
---|---|
77 | |
63 | |
47 | |
17 | |
12 |