Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I have a list of Sharepoint Libraries. I want to make a list of all the libraries not used in the past 3 months.
I have a table named 'Lists' with the columns ListName (library names) & LastEditedDate.
I created custom columns 'Previous3MonthsDay' & 'IfUsedIn3Months' as per suggestions from following post link https://community.powerbi.com/t5/Desktop/Last-3-Months-Slicer/td-p/182431
Previous3monthsDay = Date.AddMonths(DateTime.Date(DateTime.LocalNow()),-3)
IfUsedIn3Months = IF ('Lists'[LastEditedDate]
>= DATE ( YEAR ( 'Lists'[Previous3monthsDay] ), MONTH ( 'Lists'[Previous3monthsDay] ), 1 )
&& 'Lists'[LastEditedDate] <= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 ),
1, 0))
See snapshot below:
When I filter on 0 in 'IfUsedIn3Months', I get a list of all the libraries not used in the last 3 months, but the problem is, it also includes the library names which are 1 in 'IfUsedIn3Months'. (Since, ListName is not unique & can repeat based on the last edited date).
Is there any way I can get a list of 'ListName' where 'IfUsedIn3Months' = 0 & which are not there in 'IfUsedIn3Months' = 1?
Or is there any other better way to get a list of ListName (library names) which are not used in last 3 months??
Thanks in advance!
Solved! Go to Solution.
I figured out the solution as below:
Step-1. Created a calculated column:
IfNotUsed = IF(SUMX(FILTER('Lists',EXACT('Lists'[ListName],EARLIER('Lists'[ListName]))),'Lists'[IfUsedIn3Months])>0, BLANK(), 1)
(This measure returns 1 if the ListName is not used.
Filters the rows where current ListName = previous ListName -- then calculates the sum of 'IfUsedIn3Months' -- If the sum is > 0, returns blank else returns 1)
Step-2. Created a New Table:
ListsNotUsedTable = FILTER(SUMMARIZE('Lists', 'Lists'[ListName], 'Lists'[IfNotUsed]), 'Lists'[IfNotUsed]=1)
(This table Summarizes by ListName & by IfNotUsed -- then filters the table where IfNotUsed=1)
Thank you all who took out time to answer my query.
I figured out the solution as below:
Step-1. Created a calculated column:
IfNotUsed = IF(SUMX(FILTER('Lists',EXACT('Lists'[ListName],EARLIER('Lists'[ListName]))),'Lists'[IfUsedIn3Months])>0, BLANK(), 1)
(This measure returns 1 if the ListName is not used.
Filters the rows where current ListName = previous ListName -- then calculates the sum of 'IfUsedIn3Months' -- If the sum is > 0, returns blank else returns 1)
Step-2. Created a New Table:
ListsNotUsedTable = FILTER(SUMMARIZE('Lists', 'Lists'[ListName], 'Lists'[IfNotUsed]), 'Lists'[IfNotUsed]=1)
(This table Summarizes by ListName & by IfNotUsed -- then filters the table where IfNotUsed=1)
Thank you all who took out time to answer my query.
Hi @krg,
Could you please mark the proper answer as solution or share the solution if it's convenient for you? That will be a big help to the others.
Best Regards!
Dale
Hi @krg,
The question is when the 3 months start and end. For example.
Listname Date IfUsedIn3Months
A 01-12-2017 0 (no)
A 05-12-2017 1 (yes)
Will A be in the list?
Best Regards,
Dale
Hi @v-jiascu-msft,
The 3 months starts 3 months before the current date (today's date), which is calculated in the column 'Previous3monthsday'.
In the column 'IfUsedIn3Months' , ListNames which are used between 'Previous3MontshDay' & 'LastEditedDate' are marked as 1, else it's marked as 0.
If you have 0 & 1 both in 'IfUsedIn3Months', that means it has been used atleast once in the past 3 months. Then that item should not be there in the final list of Libraries not used in past 3 months.
I hope this clarifies.
Hi @krg,
There are two approaches. You can check out in this file.
A date table is needed for the time intelligence functions.
1. One measure.
OneMeasure = CONCATENATEX ( ADDCOLUMNS ( SUMMARIZE ( 'Table1', Table1[ListName] ), "Ifinthree", VAR last3list = CALCULATETABLE ( VALUES ( Table1[ListName] ), DATESINPERIOD ( 'Calendar'[Date], TODAY (), -3, MONTH ) ) RETURN IF ( [ListName] IN last3list, BLANK (), [ListName] ) ), [Ifinthree], "-" )
2. A measure and a context. And then filter the 0s out in the visual level filter.
MeasureWithContext = VAR last3list = CALCULATETABLE ( VALUES ( Table1[ListName] ), DATESINPERIOD ( 'Calendar'[Date], TODAY (), -3, MONTH ) ) RETURN IF ( MIN ( 'Table1'[ListName] ) IN last3list, 0, 1 )
Best Regards,
Dale
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |