cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MarkCBB Member
Member

Dynamic MDX for Date Range

Hello,

 

I have the following line in my query:

#"Filtered Rows" = Table.SelectRows(#"Inserted Quarter", each [Date] >= #date(2014, 1, 1) and [Date] <= #date(2016, 5, 31))

I would like to make the dates dynamic based on another Table called "Sales" the column in the Sales Table is called "DATE".

I need this to take the Min and the Max date from the Sales table and filter the query above. 

 

Is this possible?

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Dynamic MDX for Date Range

When you're in the query editor, you cannot reference a measure that has been created in DAX (just as you cannot reference a calculated column that has been created using DAX - that is a one-way-street!).

 

Assuming that the Sales table is already existent as another query, your formula would need to be written like this (in M):

 

 #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each [DATE] >= List.Min(Sales[DATE]) and [DATE] <= List.Max(Sales[DATE])),

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

6 REPLIES 6
SamLester Established Member
Established Member

Re: Dynamic MDX for Date Range

Hi, you can accomplish this by creating two measures, one for the max date from the Sales table, and one with the min date from the Sales table. You could then use these measures in your "Filtered Rows" formula.

 

MaxDate = MAX(Sales[Date])
MinDate = MIN(Sales[Date])

 

 

Thanks,
Sam Lester (MSFT)

MarkCBB Member
Member

Re: Dynamic MDX for Date Range

@SamLester I have tried your recomendation, but I dont think I am doing it correctly.

 

I create the following 2 measures:

SalesDateMax = MAX(SALES[DATE])
SalesDateMin = MIN(SALES[DATE])

and Updated the query to include these instead of the hard coded dates:

let
    Source = Excel.Workbook(File.Contents(File_Dir & "\eXceler8\Projects\Excel Add-Ins\Clients\iRam Internal\Live files\iRAM_ADDIN_APP_01\SSF\SF\SFS\1PBI_DB\Support Tables\Calendar.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet),
    #"Inserted Merged Column" = Table.AddColumn(#"Promoted Headers", "Year-Month", each Text.Combine({Text.From([Year], "en-US"), Text.From([Month_D], "en-US")}, "-"), type text),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Merged Column",{"Year-Month"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Date", type date}, {"Day of Month", Int64.Type}, {"Day of Week ID", Int64.Type}, {"Day of Week", type text}, {"Week of Year ID", Int64.Type}, {"Week of Year", type text}, {"Month_ID", Int64.Type}, {"Month", type text}, {"Quarter ID", Int64.Type}, {"Quarter", type text}, {"Year", Int64.Type}, {"Week Year", type text}, {"Month Year", type date}, {"Quarter Year", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Date", "DATE"}}),
    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each [DATE] >= SalesDateMin and [DATE] <= salesdateMax)
in
    #"Filtered Rows"

But I get the following error:

 

Expression error: The name 'SalesDateMin' wasn't recognized. Make sure it's spelled correctly.

 

Super User
Super User

Re: Dynamic MDX for Date Range

When you're in the query editor, you cannot reference a measure that has been created in DAX (just as you cannot reference a calculated column that has been created using DAX - that is a one-way-street!).

 

Assuming that the Sales table is already existent as another query, your formula would need to be written like this (in M):

 

 #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each [DATE] >= List.Min(Sales[DATE]) and [DATE] <= List.Max(Sales[DATE])),

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

MarkCBB Member
Member

Re: Dynamic MDX for Date Range

@ImkeF Thank you - That did the trick

cosuperfly Visitor
Visitor

Re: Dynamic MDX for Date Range

Hi @ImkeF ,  I am trying to accomplish this but it's not working.  Here's my MDX

let
Source = PowerBI.Dataflows([]),
#"be94dad9-11a8-4fba-a077-08e5a9b3bcee" = Source{[workspaceId="be94dad9-11a8-4fba-a077-08e5a9b3bcee"]}[Data],
#"9dc22498-b3bc-470e-b655-ac419204634a" = #"be94dad9-11a8-4fba-a077-08e5a9b3bcee"{[dataflowId="9dc22498-b3bc-470e-b655-ac419204634a"]}[Data],
#"CEO Dashboard Outpatient Details1" = #"9dc22498-b3bc-470e-b655-ac419204634a"{[entity="CEO Dashboard Outpatient Details"]}[Data],
#"Filtered Rows" = Table.SelectRows(#"CEO Dashboard Outpatient Details1", each ([Visit_Group] = "OP Clinic Visit")),
#"Filtered Rows1" = Table.SelectRows(#"CEO Dashboard Outpatient Details1", each [Visit_Date] >= List.Min(DateDim[Date]) and [Visit_Date]<= List.Max(DateDim[Date])),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Visit_Group", "FiscalYear", "HAR", "BaseClass", "PatientClass", "ApptStatus", "VisitSubGroup", "ProviderName"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"VisitTime", type time}})
in
#"Changed Type"

 

I don't get an error, but I also don't get filtered data.  I've tried using Table.Min and Table.Max but that doesn't help.  Appreciate any assistance!

Super User
Super User

Re: Dynamic MDX for Date Range

Hi @cosuperfly ,

unfortunately I have no idea on this.

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 288 members 2,773 guests
Please welcome our newest community members: