PowerQuery & MDX Dynamic Dates

Question

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 

Answer 1

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>"