MDX latest product by data greater than 0

datadtata 1 Reputation point
2020-09-11T14:17:41.737+00:00

i am trying to run an mdx for the latest date per product but at the moment it is giving latest date but our latest date is always a 0, it is normally the day previous that has latest values. Is there a way i can get my mdx to say latest date greater than 0

SELECT NON EMPTY { [Measures].[value] } ON COLUMNS, NON EMPTY {({STRTOMEMBER("[Date].[Date].&["+FORMAT(NOW(),"yyyyMMdd")+"]").Lag(61):STRTOMEMBER("[Date].[Date].&["+FORMAT(NOW(),"yyyyMMdd")+"]")} * [Product].[Product].[Product].&[Shell] * [TFL].[TFL].[TFL].ALLMEMBERS * [ID].[ID].[ID].ALLMEMBERS) } ON ROWS FROM
(
SELECT TAIL(FILTER( [Date].[Date].[Date], NOT ISEMPTY([Measures].[Value]))).ITEM(0) ON COLUMNS FROM [ID/MK]
)
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,006 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Lukas Yu -MSFT 5,796 Reputation points
    2020-09-14T07:26:53.873+00:00

    Hi,
    Have you resolved the case with Alexei's suggestion in Example ?
    If you still have problems, we are happy to know your feedback and help further.


    If the answer is helpful, please click "Accept Answer" and upvote it.

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.