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
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,
Zoe Douglas (DataZoe)



Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/

View solution in original post

5 REPLIES 5
DataZoe
Employee
Employee

@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,
Zoe Douglas (DataZoe)



Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
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,
Zoe Douglas (DataZoe)



Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/

Thanks @DataZoe! It works!

@PBI_newuser Awesome, glad to hear it! 🙂

Respectfully,
Zoe Douglas (DataZoe)



Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/

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.