question

xander85aus-7401 avatar image
0 Votes"
xander85aus-7401 asked xander85aus-7401 commented

PowerQuery & MDX Dynamic Dates

Hi, I am quite new to the PowerQuery environment and I am trying to set up some reports via the Analysis Service Cube.

Currently, we are using XLCubed to report on our Sales Cube, however I am looking to transition to PowerQuery & PowerBI.
This requires some learning in MDX, thankfully, XLCubed provides the MDX after you build a report.

So, I have the below MDX that is used via PowerQuery.
My question is, how do I set the date to be yesterday?

Also, can I define a function in the PowerQuery Code and Call on it within the MDX? To say, call on one store code only?
Even define a named range within Excel and call on that range into the MDX?
Thank you in advance.

 let Source = AnalysisServices.Database("**.***.*.*", "PRONTO-Xi Analytics", [Implementation="2.0", Query="Select Non Empty {[Measures].[Qty On Hand],[Measures].[Qty In Transit],[Measures].[Qty On PO]} On Columns,#(lf)Non Empty ( AddCalculatedMembers ( {[Stock].[Item Code].[Item Code].Members} ) * #(lf)( AddCalculatedMembers ( {[Stock].[Supplier Name].[Supplier Name].Members} ) * #(lf)AddCalculatedMembers ( {[Warehouse].[Store Code].[Store Code].Members} ) ) ) On Rows From [Sales Cube] Where ([Period].[Financial Year].[Date].&[2021-03-23T00:00:00]) Cell Properties VALUE"]) in Source 
power-query-not-supported
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

Ehren avatar image
0 Votes"
Ehren answered xander85aus-7401 commented

To insert yesterday's date instead of the hard-coded date shown above, you can add -1 days, format the date/time, and concatenate it into the query text. For example:

"<original DAX here, up until where the hard-coded date was>" & DateTime.ToText(DateTime.From(DateTime.Date(Date.AddDays(DateTime.LocalNow(), -1))), "yyyy'-'MM'-'dd'T'HH':'mm':'ss") & "<the rest of the DAX that was after the hard-coded date>"

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi, thank you for this @Ehren !
I have been able to achieve what I wanted using something close to this.

Within the DAX, I was able to do this.

YESTERDAY = DateTime.ToText(DateTime.From(DateTime.Date(Date.AddDays(DateTime.LocalNow(), -1))), "yyyy'-'MM'-'dd'T'HH':'mm':'ss")

Then whenever the Period Date measure form the Cube is used
([Period.Date]) = "[Period].[Date].&[" & YESTERDAY & "]" )

I can effectively use this for any measure to set dynamic filters within the Cube

I do, however, have another question.
Can I reference another table/query to set a value in the next query? So I can set a query that defines the date and pulls the week syntax from our Cube. Then define that as a variable and call on it?

0 Votes 0 ·
Ehren avatar image Ehren xander85aus-7401 ·

Yes, you can reference other queries from the current query.

0 Votes 0 ·

Thanks! A quick google search and it was easy enough! This is fantastic. Your assistance has led to more dynamically changing reports.

0 Votes 0 ·