Filter table visual bewteen date range based on slicer

[datapy]

Hi, I am having difficulties to do the following in Power BI Desktop :

I would like to make a table visual which is being filtered on a date range based on a date slicer visual.

Here is an example :

Date selected in date slicer : 03/01/2024

Date range for filtering table visual is 3 days prior the selected date: from 01/01/2024 until the 01/03/2024 included

I tried to following DAX code :

MeasureAmount =

var currentDate = SELECTEDVALUE(FactTable[date])

var startDate   = currentDate - 2

var sumAmount   =

CALCULATE

(

    SUM(FactTable[amount]),

    DATESBETWEEN

    (

        FactTable[date],

        startDate,

        currentDate

    )

)

return sumAmount

This measure works fine when I display it on a table visual. In the screenshot below, the measure returns 2774 which is the expect output. However, when I add other columns such as date and idSale, the data shown is not what I am expecting. In my example, only data for the 03/01/2024 is displayed.

Picture 1

The expected result is the following :

Picture 2

Any ideas how to solve this ?

Many thanks

[Ashish_Mathur]

Hi,

Share data in a format that can be pasted in an MS Excel file.

[v-zhouwen-msft · Accepted solution]

Hi @datapy ,

The Table data is shown below:

Please follow these steps:

1. Use the following DAX expression to create a table 

Table 2 = CALENDAR(DATE(2024,1,1),DATE(2024,1,6))

2. Use the following DAX expression to create a measure

MeasureAmount = 
var currentDate = SELECTEDVALUE('Table 2'[Date])
var startDate   = currentDate - 2
var sumAmount   = CALCULATE(SUM('Table'[Amount]),DATESBETWEEN('Table'[Date],startDate,currentDate))
RETURN sumAmount

3.Final output

Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[datapy]

Hi, many thanks for your help. It works now!