cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper V
Helper V

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
Highlighted
Super User IV
Super User IV

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])),

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

6 REPLIES 6
Highlighted
Microsoft
Microsoft

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)

Highlighted
Helper V
Helper V

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.

 

Highlighted
Super User IV
Super User IV

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])),

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

Highlighted
Helper V
Helper V

Re: Dynamic MDX for Date Range

@ImkeF Thank you - That did the trick

Highlighted
Regular 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!

Highlighted
Super User IV
Super User IV

Re: Dynamic MDX for Date Range

Hi @cosuperfly ,

unfortunately I have no idea on this.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Helpful resources

Announcements
Ignite

Microsoft Ignite

This will be a conference that you do not want to miss!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Don't miss the Power BI Dev Camp this week!

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors