cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PBI_newuser
Post Prodigy
Post Prodigy

How to include last 30 days data in last 60 days category

Hi,

I have a table as shown below.

How to create a field to show Last 30 days, Last 60 days, Last 90 days and Last 180 days data?


Example:

When user selects "Last 90 days", Job A-123, B-123, C-123 should show up.

When user selects "Last 60 days", Job A-123, B-123 should show up.

When user selects "Last 30 days", Job A-123 should show up.

 

JobDays DifferenceCategory
A-123250 - 30 days
B-1233831- 60 days
C-1238661- 90 days
D-12313091 -180 days
E-12315791 -180 days
F-123190> 180 days
1 ACCEPTED SOLUTION

@PBI_newuser Looks like you had just missed the relationship between the tables step:

DataZoe_0-1618495803520.png

Once that was in place, it worked. Please see attached.

Respectfully,
DataZoe


See my reports and blog at https://www.datazoepowerbi.com/

View solution in original post

5 REPLIES 5
DataZoe
Super User II
Super User II

@PBI_newuser I would try this approach:

1. Create a table with the Last X Days mapped to your Days Difference (Modeling > New Table)

Last X Days Slicer =
union(
ADDCOLUMNS(GENERATESERIES(1,30),"Last X Days","Last 30 Days"),
ADDCOLUMNS(GENERATESERIES(1,60),"Last X Days","Last 60 Days"),
ADDCOLUMNS(GENERATESERIES(1,90),"Last X Days","Last 90 Days"),
ADDCOLUMNS(GENERATESERIES(1,max('Table'[Days Difference])),"Last X Days","All Days"))

 

2. Then create a relationship between your Table and this new table on the common Days Difference column. This will need to be many to many, the direction has to be that Last X Days Slicer filters Table.

 

DataZoe_0-1618452890076.png

3. Now you can use your Last X Days field as a slicer to have the jobs filter appropriately. 

DataZoe_1-1618452932059.png

 

 

 

Respectfully,
DataZoe


See my reports and blog at https://www.datazoepowerbi.com/

Hi @DataZoe , thanks for the solution. But it doesn't work for my data.
Here is the sample. Could you please take a look at it?

@PBI_newuser Looks like you had just missed the relationship between the tables step:

DataZoe_0-1618495803520.png

Once that was in place, it worked. Please see attached.

Respectfully,
DataZoe


See my reports and blog at https://www.datazoepowerbi.com/

View solution in original post

Thanks @DataZoe! It works!

@PBI_newuser Awesome, glad to hear it! 🙂

Respectfully,
DataZoe


See my reports and blog at https://www.datazoepowerbi.com/

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Kudoed Authors