Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Memorable Member
Memorable Member

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors