Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

2 ACCEPTED SOLUTIONS
Anonymous
Not applicable

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

View solution in original post

Anonymous
Not applicable

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

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

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
Not applicable

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

Anonymous
Not applicable

awesome! Glad was able to help 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.