cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AshutoshSahoo_1
Frequent Visitor

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

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 

1 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)

View solution in original post

5 REPLIES 5
AshutoshSahoo_1
Frequent Visitor

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

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

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

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
Super User
Super User

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

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.