Share via

MDX code to get today's date

L G 1 Reputation point
2020-08-21T14:09:13.197+00:00

Hello,

I created a query for Excel in Power Pivot using the visual designer. I currently have to change the dates every month.

I need to modify it so it automatically returns the data for January from the current year until the current month.

[Time].[By Month].[Date].&[2020-01-01T00:00:00] : [Time].[By Month].[Date].&[2020-08-01T00:00:00]

Thanks in advance

LG

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,290 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Alexei Stoyanovsky 3,416 Reputation points
    2020-08-22T19:29:05.887+00:00

    StrToMember("[Time].[By Month].[Date].&[" & Format(Now(),"yyyy") & "-01-01T00:00:00]") : StrToMember("[Time].[By Month].[Date].&[" & Format(Now(),"yyyy-mm") &"-01T00:00:00]")

    1 person found this answer helpful.
    0 comments
  2. Lukas Yu -MSFT 5,821 Reputation points
    2020-08-24T05:43:05.687+00:00

    You could also try YTD function as :

    YTD(StrToMember("[Time].[By Month].[Date].&[" & Format(Now(),"yyyy-mm") &"-01T00:00:00]"))

    0 comments

Your answer

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