Power BI JavaScript filter between 2 dates

[mak82pk]

var Filter =
[
{
"$schema": "http://powerbi.com/product/schema#advanced",
"target": {
"table": "Query1",
"column": "Date"
},
"logicalOperator": "And",
"conditions":
[
{
"operator": "GreaterThanOrEqual",
"value": "2017-11-23T00:00:00.000Z"
}
,
{
"operator": "LessThanOrEqual",
"value": "2017-11-30T00:00:00.000Z"
}]
},
{
"$schema": "http://powerbi.com/product/schema#basic",
"target": {
"table": "Query1",
"column": "Hotel"
},
"operator": "In",
"values": ["Versace"]

}
];

// Embed configuration used to describe the what and how to embed.
// This object is used when calling powerbi.embed.
// This also includes settings and options such as filters.
// You can find more information at https://github.com/Microsoft/PowerBI-JavaScript/wiki/Embed-Configuration-Details.
var config = {
type: 'report',
accessToken: accessToken,
embedUrl: embedUrl,
id: reportId,
settings: {
filterPaneEnabled: false,
navContentPaneEnabled: true
}
};

// Grab the reference to the div HTML element that will host the report.
var reportContainer = document.getElementById('reportContainer');

// Embed the report and display it within the div container.
var report = powerbi.embed(reportContainer, config);

// Report.on will add an event handler which prints to Log window.
report.on("loaded", function () {
report.off("loaded");
report.getFilters()
.then(filters => {
return report.setFilters(Filter);
})
});

I am using the above code to filter the report to display data for 1 week only but it the condition 

{
"operator": "GreaterThanOrEqual",
"value": "2017-11-23T00:00:00.000Z"
}
,
{
"operator": "LessThanOrEqual",
"value": "2017-11-30T00:00:00.000Z"
}

IS NOT WORKING

 

I would be grateful if your provide the solution at the earliest.

Thanks in advance

[Eric_Zhang]

@mak82pk

Per my test, you can try

var Filter = {
 $schema: "http://powerbi.com/product/schema#advanced",
 target: {
 table: "Table1",
 column: "Date"
 },
 logicalOperator: "And",
 conditions: [{
 operator: "LessThanOrEqual",
 value: "2017-11-04 00:00:00"
 },
 {
 operator: "GreaterThanOrEqual",
 value: "2017-11-03 00:00:00"
 }
 ]
 }

The table and column names are case sensitive, please ensure that the names are correct in your case. Also, please enable the filter panel(filterPaneEnabled:true) for debugging. When the filter is applied you'll see something similar below in the filter panel.

By the way, in your case, you may remove the trailing Z in the date string, see Power BI Offsetting DateTime filter passed via Json Power BI Embedded

[mak82pk]

First of all thank you very much for providing me with the solution

the line chart is filtering the data but the calendar is not getting updated

        var Filter =
            [
                
                {
                    $schema: "http://powerbi.com/product/schema#advanced",
                    target: {
                        table: "Query1",
                        column: "Date"
                    },
                    logicalOperator: "And",
                    conditions: [{
                        operator: "LessThanOrEqual",
                        value: "2017-11-30 00:00:00"
                    },
                    {
                        operator: "GreaterThanOrEqual",
                        value: "2017-11-23 00:00:00"
                    }
                    ]

                },
                {
                    $schema: "http://powerbi.com/product/schema#basic",
                    target: {
                        table: "Query1",
                        column: "Hotel"
                    },
                    operator: "In",
                    values: ["Versace"]

                }
                
            ];

I tried to apply filters on Power bi desktop as well.

Thanks once again for the help

[mak82pk]

I would be grateful if someone provides me with the solution.

[Eric_Zhang · Accepted solution]

@mak82pk

In the case "the line chart is filtering the data but the calendar is not getting updated", based on my test, if the filter is applied to your bussiness/transaction data, the "cross filter direction" should be "Both". In "Single" case, the behavior in your case is expected. In Single, you shall filter the calendar table instead.

[mak82pk]

First of all thank you very much for providing me with the solution

the line chart is filtering the data but the calendar is not getting updated

        var Filter =
            [
                
                {
                    $schema: "http://powerbi.com/product/schema#advanced",
                    target: {
                        table: "Query1",
                        column: "Date"
                    },
                    logicalOperator: "And",
                    conditions: [{
                        operator: "LessThanOrEqual",
                        value: "2017-11-30 00:00:00"
                    },
                    {
                        operator: "GreaterThanOrEqual",
                        value: "2017-11-23 00:00:00"
                    }
                    ]

                },
                {
                    $schema: "http://powerbi.com/product/schema#basic",
                    target: {
                        table: "Query1",
                        column: "Hotel"
                    },
                    operator: "In",
                    values: ["Versace"]

                }
                
            ];

I tried to apply filters onPower bi desktop as well.

Thanks once again for the help

[mak82pk]

I also tried GreaterThan and LessThan operators