Analysis Services as Power Query source - convert YYYYMM string to date in MDX

claudio chignoli 1 Reputation point
2022-05-13T13:42:18.157+00:00

I need to covert YYYYMM string ( [DimDate].[Calculation Period].[Calculation Period]) to date in MDX when importing data from Analysis Service OLAP database to Power Query (to keep the Native Query active and apply incremental refresh)
Here the native query

SELECT
    {
        [Measures].[Quantity],
        [Measures].[Sales YTD],
        [Measures].[Sales]
    }ON 0,
    ORDER(
        NONEMPTY(
            CROSSJOIN(
                CROSSJOIN(
                    CROSSJOIN(
                        CROSSJOIN(
                            CROSSJOIN(
                                CROSSJOIN(
                                    CROSSJOIN(
                                        CROSSJOIN(
                                            CROSSJOIN(
                                                CROSSJOIN(
                                                    CROSSJOIN(
                                                        CROSSJOIN(
                                                            [DimDate].[Calculation Period].[Calculation Period].ALLMEMBERS,
                                                            ADDCALCULATEDMEMBERS(
                                                                DESCENDANTS(
                                                                    [DimDate].[Date Hierarchy].[Year].ALLMEMBERS,
                                                                    [DimDate].[Date Hierarchy].[Month Desc],
                                                                    LEAVES
                                                                )
                                                            )
                                                        ),
                                                        [DimDate].[Month].[Month].ALLMEMBERS
                                                    ),
                                                    [DimDate].[Year].[Year].ALLMEMBERS
                                                ),
                                                [DimDistributionChannel].[Channel].[Channel].ALLMEMBERS
                                            ),
                                            [DimMarketOrganization].[Factory].[Factory].ALLMEMBERS
                                        ),
                                        [DimProductLine].[Code].[Code].ALLMEMBERS
                                    ),
                                    [DimProductLine].[PRODUCT1].[PRODUCT1].ALLMEMBERS
                                ),
                                [DimProductLine].[PRODUCT2].[PRODUCT2].ALLMEMBERS
                            ),
                            [DimProductLine].[PRODUCT3].[PRODUCT3].ALLMEMBERS
                        ),
                        [DimProductLine].[PRODUCT].[PRODUCT].ALLMEMBERS
                    ),
                    [DimProductLine].[Product Line Hierarchy].[PL Division].ALLMEMBERS
                ),
                [DimProductOrganization].[Organization].[Organization].ALLMEMBERS
            ),
            {
                [Measures].[Quantity],
                [Measures].[Sales YTD],
                [Measures].[Sales]
            }
        ),
        ANCESTOR(
            [DimDate].[Date Hierarchy].CURRENTMEMBER,
            [DimDate].[Date Hierarchy].[Year]
        ).MEMBER_CAPTION,
        BDESC
    )
    PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON 1
FROM [xxxxx] CELL PROPERTIES VALUE

Could you please help me with how to convert [DimDate].[Calculation Period].[Calculation Period] to date format (like YYYY+MM+01)

Thanks a lot

Azure Analysis Services
Community Center | Not monitored
{count} votes

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.