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
AlanB34
Regular Visitor

Creating a table to Show Items Created post x number of days from sharepoint list

Hi all,

 

I currently use Power BI connected to a sharepoint online list that users use to capture case information. Cases have 3 status - New, Open & Closed

 

I am looking to build a visuale table in Power BI to show me all cases that are open for more than 35 days? 

 

Any suggestions/tips would be greatly appreciated! 

1 ACCEPTED SOLUTION
v-cgao-msft
Community Support
Community Support

Hi @AlanB34 ,

 

I use this table for testing.

vcgaomsft_0-1652433300584.png

Please new a measure.

Measure = IF(DATEDIFF(MAX('Table'[Case creation time]),TODAY(),DAY)>35,1)

Setting of the filter.

vcgaomsft_1-1652433449324.png

Attached PBIX file for reference.

Best Regards,
Gao

Community Support Team

 

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

View solution in original post

4 REPLIES 4
v-cgao-msft
Community Support
Community Support

Hi @AlanB34 ,

 

I use this table for testing.

vcgaomsft_0-1652433300584.png

Please new a measure.

Measure = IF(DATEDIFF(MAX('Table'[Case creation time]),TODAY(),DAY)>35,1)

Setting of the filter.

vcgaomsft_1-1652433449324.png

Attached PBIX file for reference.

Best Regards,
Gao

Community Support Team

 

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

rbriga
Impactful Individual
Impactful Individual

You may create a measure such as

Days Open=
CALCULATE(
DATEDIFF(Today(), Table[Creation Date],DAY),
KEEPFILTERS(Table[Status] = "Open")
)

Next, you can add this measure as a filter on the visual (is greater than 35).

This returns the 

-------------------------
Data analyst by day, hockey goalie by night.
Did I help? Then please hit that "kudos" or "accept as a solution" button!

Hey, Thank for the advice. I tried the measure below however i get an error around the Table[Creation date] CALCULATE(DATEDIFF(TODAY(), 'MI DATABASE'[Created],DAY),KEEPFILTERS('MI Database'[Case Status] Error: A single value for column 'Created' in table 'MI DATABASE' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result. For reference the creation date field is displayed as 'MI DATABASE'[Created] Thanks again
rbriga
Impactful Individual
Impactful Individual

Right, that's an issue.

The easiest way around it would be to create a duration column for us to work with (if the status is open), either in PowerQuery or- if possible- at the source (for example, a SQL view).

Now you can just put this field in the filter pane with "greater than 35".

-------------------------
Data analyst by day, hockey goalie by night.
Did I help? Then please hit that "kudos" or "accept as a solution" button!

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.