如何使用MDX获取每月的上月最后一天？

Hey there! I totally get the frustration when tail() doesn't behave as expected for grabbing the last day of the prior month. Let's break down what might be going wrong and get you a working solution.

Why Your tail() Approach Might Be Failing #

The tail() function just grabs the last N items from a collection—but if you're not narrowing down that collection to only the dates from the previous month, or if your date set isn't sorted correctly, you'll end up with the wrong date (like the very last date in your entire date dimension instead of last month's end).

Working Solutions #

Here are two reliable ways to get the last day of the previous month in MDX:

1. Direct Date Calculation (No Dimension Dependency)

If you don't need to pull the date from your cube's date dimension, use MDX's built-in date functions to calculate it directly:

WITH 
MEMBER [Measures].[Last Day of Previous Month] AS
    // Subtract 1 month from today, then get the end of that month
    DateEndOfMonth(DateAdd("Month", -1, Now()))
SELECT 
[Measures].[Last Day of Previous Month] ON 0
FROM [YourCubeName]

This will return the actual date value (e.g., 2024-05-31 if today is in June 2024) without relying on your date dimension's structure.

2. Pull from Your Date Dimension

If you need to reference the actual member from your date dimension (for filtering or other calculations), use a filter to isolate last month's dates first, then grab the tail:

WITH 
MEMBER [Measures].[Last Day of Previous Month] AS
    Tail(
        // Filter to only dates in the previous month
        Filter(
            [Date].[Date].[Date].Members,
            // Adjust the date key comparison to match your dimension's key format (YYYYMMDD is common)
            [Date].[Date].CurrentMember.Member_Key >= Format(DateStartOfMonth(DateAdd("Month", -1, Now())), "yyyyMMdd")
            AND [Date].[Date].CurrentMember.Member_Key <= Format(DateEndOfMonth(DateAdd("Month", -1, Now())), "yyyyMMdd")
        ), 1 // Grab only the last item from the filtered set
    ).Item(0).Member_Caption // Get the readable date string
SELECT 
[Measures].[Last Day of Previous Month] ON 0
FROM [YourCubeName]

Just make sure to adjust [Date].[Date].[Date] to match your actual date hierarchy, and tweak the Member_Key format to align with how dates are stored in your dimension (e.g., if your keys are 20240531 instead of a date object, the Format() function will make the comparison work).

Quick Checks to Avoid Future Issues #

• Sort Your Date Set: If you use tail() on an unsorted collection, it won't return the latest date—always ensure your date members are ordered ascending (most date dimensions are by default, but double-check!).
• Validate Date Ranges: Use DateStartOfMonth() and DateEndOfMonth() to clearly define the bounds of the previous month, so you don't accidentally include dates from other months.

内容的提问来源于stack exchange，提问作者walid gamal