How to Get the Last 12 Dates fields in Power BI report Builder

[AshutoshSahoo_1]

Hi Experts,

                 I m having  a date field  in my data in my parameter if I m selecting a date I want to show the last 12 dates  from the data

Ex: 1/1/22

5/1/22

7/1/22

12/2/22

17/2/22

31/3/22

1/5/22

8/7/22

5/9/22

. so on

if i m selecting 8/7/22 i want previous 12 dates from the date field.

Please help me to get the solution.

@dax 

[AshutoshSahoo_1]

Hi Thanks for the response @josef78  but I m getting the proper output can you please explain more? by giving some examples.

[josef78]

well, but only for confirm, are you talking about report builder (paginated report)? And, is connected to SQL data source or SSAS cube?

[AshutoshSahoo_1]

Yes Report builder and i m connecting through Dsn from SQL database.

[josef78 · Accepted solution]

e.g. if you have table DATA with collumn DATE 

1) create parameter @date 

2) add where condition to your sql dataset like:

select ... from DATA

where DATE in (select distinct top 12 DATE from DATA where DATE <= @date order by DATE desc)

[josef78]

somethink with additional (hidden) parameter or subquery, where you select top 12 date from datetable where date <= @date order by date desc