Hello - I used the "Download with Live Connection" option to get the data from a Power BI Report into Excel. The code below is the code for this Live Connection. Since it is a Connection and not a Query, is there a way to modify this code so that the Start Date and End Date would be connected to cells (one for Start Date and the other for End Date) in the Excel file first and then go grab the Power BI Report data (upon Refresh)?
DEFINE
VAR __DS0FilterTable =
TREATAS({"'Date Dim'[Fiscal Month Year]"}, 'Parameter - Time'[Parameter - Time Fields])
VAR __DS0FilterTable2 =
TREATAS(
{"'Dimension Sets Pivot'[Dimension Set 4]",
"'Dimension Sets Pivot'[Dimension Set 7]"},
'Parameter - Dimension Sets'[Dimension Sets Fields]
)
VAR __DS0FilterTable3 =
TREATAS(
{"'Measure'[GL Amount]",
"'Measure'[Debit Amount]",
"'Measure'[Credit Amount]"},
'Parameter - Values'[Parameter - Values Fields]
)
VAR __DS0FilterTable4 =
TREATAS(
{"'General Ledger Accounts'[Account Category]",
"'General Ledger Accounts'[Custom Accounting Subcategory]",
"'General Ledger Accounts'[Company Name]",
"'General Ledger Accounts'[Custom Accounting Totals]",
"'General Ledger Accounts'[Custom Accounting Category]",
"'General Ledger Accounts'[Account Subcategory Descript]",
"'General Ledger Accounts'[Name]",
"'General Ledger Accounts'[Number]"},
'Parameters - Account '[Parameters - Account Fields]
)
VAR __DS0FilterTable5 =
FILTER(
KEEPFILTERS(VALUES('Date Dim'[FiscalBaseDate])),
AND(
'Date Dim'[FiscalBaseDate] >= DATE(2024, 1, 1),
'Date Dim'[FiscalBaseDate] < DATE(2024, 2, 15)
)
)
VAR __DS0FilterTable6 =
TREATAS({TRUE}, 'Date Dim'[Is Past])
VAR __DS0Core =
SUMMARIZECOLUMNS(
'General Ledger Accounts'[Account Category],
'General Ledger Accounts'[Category Sort Order],
'General Ledger Accounts'[Custom Accounting Subcategory],
'Date Dim'[Fiscal Month Year],
'Date Dim'[Month Offset],
'General Ledger Accounts'[Company Name],
'General Ledger Accounts'[Custom Accounting Totals],
'General Ledger Accounts'[Custom Accounting Category],
'General Ledger Accounts'[Custom Acc. Category Sort],
'General Ledger Accounts'[Account Subcategory Descript],
'Dimension Sets Pivot'[Dimension Set 4],
'Dimension Sets Pivot'[Dimension Set 7],
'General Ledger Accounts'[Name],
'General Ledger Accounts'[Number],
__DS0FilterTable,
__DS0FilterTable2,
__DS0FilterTable3,
__DS0FilterTable4,
__DS0FilterTable5,
__DS0FilterTable6,
"GL_Amount", 'Measure'[GL Amount],
"Debit_Amount", 'Measure'[Debit Amount],
"Credit_Amount", 'Measure'[Credit Amount]
)
VAR __DS0BodyLimited =
TOPN(
500000,
__DS0Core,
'General Ledger Accounts'[Category Sort Order],
1,
'General Ledger Accounts'[Account Category],
1,
'General Ledger Accounts'[Custom Accounting Subcategory],
1,
'Date Dim'[Month Offset],
1,
'Date Dim'[Fiscal Month Year],
1,
'General Ledger Accounts'[Company Name],
1,
'General Ledger Accounts'[Custom Accounting Totals],
1,
'General Ledger Accounts'[Custom Acc. Category Sort],
1,
'General Ledger Accounts'[Custom Accounting Category],
1,
'General Ledger Accounts'[Account Subcategory Descript],
1,
'Dimension Sets Pivot'[Dimension Set 4],
1,
'Dimension Sets Pivot'[Dimension Set 7],
1,
'General Ledger Accounts'[Name],
1,
'General Ledger Accounts'[Number],
1
)
EVALUATE
__DS0BodyLimited
ORDER BY
'General Ledger Accounts'[Category Sort Order],
'General Ledger Accounts'[Account Category],
'General Ledger Accounts'[Custom Accounting Subcategory],
'Date Dim'[Month Offset],
'Date Dim'[Fiscal Month Year],
'General Ledger Accounts'[Company Name],
'General Ledger Accounts'[Custom Accounting Totals],
'General Ledger Accounts'[Custom Acc. Category Sort],
'General Ledger Accounts'[Custom Accounting Category],
'General Ledger Accounts'[Account Subcategory Descript],
'Dimension Sets Pivot'[Dimension Set 4],
'Dimension Sets Pivot'[Dimension Set 7],
'General Ledger Accounts'[Name],
'General Ledger Accounts'[Number]
