Need help with a DAX query

[Anonymous]

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

[Anonymous · Accepted solution]

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 "/"

[Anonymous · Accepted solution]

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 "/"

[Anonymous · Accepted solution]

Great, thank you very much!  I just created the calendar table and utilize the time intelligent functions

[Anonymous]

awesome! Glad was able to help