I just figured out that I asked the wrong question. The
Date/Year, Date/Month etc.
parts in the URL-filter refer to a table in the query and a column inside that table. I didn't know that until today and since I have a "Date" column in my "Date" table, I could easily solve this by building the filter like this:
$filter=Date/Date ge " + startYear + "-" + startMonthStr + "-01 and Date/Date lt " + endYear + "-" + endMonthStr + "-01"
If anyone ever encounters a similar problem, here is the whole script:
<script>
document.addEventListener("DOMContentLoaded", function() {
var pageTitle = document.title;
var match = pageTitle.match(/\((\d{2})-(\d{4})\)/);
if (match) {
var month = parseInt(match[1]);
var year = parseInt(match[2]);
var startYear = year;
var startMonth = month - 3;
var endYear = year;
var endMonth = month;
if (startMonth <= 0) {
startMonth = startMonth + 12;
startYear--;
}
var startMonthStr = startMonth < 10 ? "0" + startMonth : startMonth;
var endMonthStr = endMonth < 10 ? "0" + endMonth : endMonth;
var dynamicURL = "https://app.powerbi.com/reportEmbed?reportId=XXX&appId=XXX&autoAuth=true&ctid=XXX&navContentPaneEnabled=false&pageName=ReportSectionXXX&$filter=Date/Date ge " + startYear + "-" + startMonthStr + "-01 and Date/Date lt " + endYear + "-" + endMonthStr + "-01";
document.getElementById("dashboardIframe").src=dynamicURL;
}
});
</script>
<body>
<iframe id="dashboardIframe" title="Title" width="500" height="1500"></iframe>
</body>
This script loads when the rest of the page is loaded (the EventListener does that) and creates the URL of an embedded PowerBI report from a string in the document title that consists of month and year (e.g. 02-2024).
There might be a smarter way, idk, but this works. Replace XXX in the URL with the IDs of your report, delete all date-filters in the report you want to filter while embedded, put the code in an html-box in confluence (if available, might need to ask admin) and it should work.
