I recently had a client give us a call asking for helping creating a calculation in her cube to show the month to date value for the previous year. Here’s the basics of the calculation I created.
View last week’s #MDXMonday -> Calculating Daily Average Sales
This calculation involve two parts. First we must create a calculation that figures the month to date value of the measure in question. To do this I used the MTD function along with the Aggregate function to roll up the Internet Sales Amount for each day in a month.
Bạn đang xem: month to date là gì
Đang hot: Mơ thấy ông nội có ý nghĩa gì, mơ thấy ông nội đánh lô đề con gì?
** Keep in mind that your date dimension and date attribute Type properties must be configured correctly in order for you to be able to use MDX time functions such as MTD, WTD, QTD, and YTD.
Now that we have the correct MTD calculation, but we need to modify this calculation to retrieve the MTD numbers for the previous year. To do that we can use the ParallelPeriod function. The ParallelPeriod function is used to navigate along a calendar hierarchy at a particular level. In this case we can use ParallelPeriod to go one member back on the Year level of the Calendar hierarchy like so:
Đang hot: Giá cá diêu hồng bao nhiêu tiền 1kg hiện nay?
And that’s how you create a calculation to figure the month to date for the previous year. Just an FYI, this calculation could also be used to calculate the previous year to date by swapping the MTD function for the YTD function.
Resources
- View last week’s #MDXMonday -> Calculating Daily Average Sales
- MDXpert.com: Great website with examples for most of the MDX functions and links back to Technet. Check it out.
Feedback?
Got any feedback on this blog post? Or do you have a calculation you’d like me to work on? Let me know in the comments! Thanks!
Đang hot: Giải mã giấc MƠ THẤY HỔ đánh con gì? | điềm LÀNH hay DỮ?