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

@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.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!