Forum Discussion
Need help with a DAX query
Hello,
I need to calculate the [Net Assets] both the first day and last day of each month, which is my [As Of Date] column within the dataset, based on Account Number.
Below is the formula in high level form
(NetAssets(FirstDayOfMonth) + NetAssets(LastDayOfMonth) / 2) x ManagementFee x (NumberOfDaysInMonth / 365)
At first glance, I believe I'll need separate mesures that would give me the FirstDayOfMonth and LastDayOfMonth from the [As of Date] column as well as the NumberOfDaysInMonth.
Any help would be much appreciated.
Thank you
First thing would be to use a dedicated calendar table and utilize the time-intelligence functions which woud make your requirement exponentially easier
https://docs.microsoft.com/en-us/dax/time-intelligence-functions-dax
also, I'd use the Divide Function instead of "/"
3 Replies
First thing would be to use a dedicated calendar table and utilize the time-intelligence functions which woud make your requirement exponentially easier
https://docs.microsoft.com/en-us/dax/time-intelligence-functions-dax
also, I'd use the Divide Function instead of "/"