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 have a single date column where I need to get the total number of days between the min and max date in that column. I have tried several formulas but nothing seems to get the correct result. I need a function or calculation that gives NUMBER OF DAYS BETWEEN-- I tried DATESBETWEEN, DATESINPERIOD, etc. but they all return a date and not a number of days. This is what I have guessed (don't laugh 😋) so far:
Days In Period = **insert function**(MIN('HP Evo Power BI Data'[DATE ENTERED].[Date],MAX('HP Evo Power BI Data'[DATE ENTERED].[Date])))
Thanks --Sam
Solved! Go to Solution.
Hi @sammi1244,
DAX is not really my specialty but I think this will work.
(change field names as required obviously)
Measure =
VAR MinDate = MIN(financials[Date])
VAR MaxDate = MAX(financials[Date])
RETURN
DATEDIFF(MinDate, MaxDate, DAY)
Hope this helps.
Kim
Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
Hi,
Write this as a calculated column formula in yourHP Evo Power BI Data table
=1*(MAX('HP Evo Power BI Data'[DATE ENTERED])-MIN('HP Evo Power BI Data'[DATE ENTERED]))
Hope this helps.
Thank you! I just prefered to not create a new column so I went with @KNP suggestion. Thanks for chiming in. I apprecate it.
Hi @sammi1244,
DAX is not really my specialty but I think this will work.
(change field names as required obviously)
Measure =
VAR MinDate = MIN(financials[Date])
VAR MaxDate = MAX(financials[Date])
RETURN
DATEDIFF(MinDate, MaxDate, DAY)
Hope this helps.
Kim
Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
So this is perfect; exactly what I want. 'Key Measures/Calculation'[Days In Period]' is the measure we created and it is used in my next measure:
Avg Days in AR = (AVERAGE('HP Evo Power BI Data'[TRANSACTION AMOUNT])/ sum('HP Evo Power BI Data'[Total Payments Amount]) * 'Key Measures/Calculation'[Days In Period])
But this results in 0 (zero). Anyone with feedback on what I did wrong?
Glad that first part worked.
It would probably be easier to see the issue if you could post a sample dataset but I would probably start by turning the [TRANSACTION AMOUNT] and [Total Payments Amount] into measures and see if they return what is expected. It's always easier to debug DAX (or any) issues in smaller bits.
Also, best to use DIVIDE instead of '/' as it can deal with divide by zero issues and allows you to pass an alternate value. Other than that, without seeing the data and expected output it is difficult to offer any more than that.
Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
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 |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |