Count Something in Last N Days

[CW112358]

I want to count Status of pending tasks in the last 7 days, 14 days, 30 days, 60 days, etc.  I want to turn that into a slicer/filter for using on an entire page that has 6 graphs on it.  I have been searching for a week now, trying things and they aren't working.  Can anyone assist  with the steps.  I have a dataset with multiple columns, has Status, Date it was created (has a time stamp too), etc.  What I was seeing is you have to create a Date table and then a Date/Period Table but I can't for the life of me get it to work.  Everytime I click it to make it work, it just shows a blank screen.  I've checked all the tables multiple times and by hand and in my head I can make it work but on Power Bi I can't.

[V-pazhen-msft]

@CW112358 

Once you have a date column, you can create a column for the periods as the slicer and then create a measure to count status. 

Last N Days = SWITCH(TRUE(),
[Date]<=TODAY()&&[Date]>=TODAY()-7,7,
[Date]<=TODAY()&&[Date]>=TODAY()-14,14,
[Date]<=TODAY()&&[Date]>=TODAY()-30,30,
[Date]<=TODAY()&&[Date]>=TODAY()-60,60,
[Date]<=TODAY()&&[Date]>=TODAY()-90,90)

Count Pending = CALCULATE(COUNTROWS('Table'),FILTER('Table',[Status]="Pending"))

Check my sample: https://qiuyunus-my.sharepoint.com/:u:/g/personal/paul_qiuyunus_onmicrosoft_com/EWqqgAHocgxMkkMroHt3...

Paul Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[CW112358]

FileSystemObjectType	Id	AD_Priority	AD_Assigned To	AD_Date Completed	AD_Status	AD_Completed By	SR_Category	Pending Email Sent	ID.1	Modified	Created
0	33	Normal	BM	11/19/2019 8:00	Completed	BM	Software	No	33	11/19/2019 20:35	11/13/2019 18:14
0	44	Normal	AC	2/5/2020 8:00	Completed	AC	Other	No	44	2/5/2020 22:02	11/18/2019 21:30
0	45	Normal	BM	11/19/2019 8:00	Completed	BM	Data	No	45	11/19/2019 20:36	11/19/2019 14:15
0	51	Normal	MS	12/12/2019 8:00	Completed	MS	Data	No	51	2/4/2020 20:33	11/20/2019 14:34
0	54	Normal	AC	12/2/2019 8:00	Completed	AC	Software	No	54	12/2/2019 20:30	11/20/2019 17:54
0	56	Normal	ME		Completed	ME	Data	No	56	11/21/2019 18:38	11/20/2019 19:51
0	58	Normal	AC	11/21/2019 8:00	Completed	AC	Software	No	58	11/21/2019 22:11	11/21/2019 19:51
0	60	Normal	ME	11/26/2019 8:00	Completed	ME	Data	No	60	12/2/2019 17:48	11/25/2019 15:44
0	61	Normal	AC	11/25/2019 8:00	Completed	AC	Data	No	61	11/25/2019 19:35	11/25/2019 19:17
0	64	Normal	AC	11/27/2019 8:00	Completed	AC	Data	No	64	11/27/2019 18:20	11/27/2019 17:56
0	67	Normal	MS		Completed	MS	Software	No	67	2/4/2020 20:35	12/1/2019 21:18
0	69	Normal	AC	12/3/2019 8:00	Completed	AC	Data	No	69	12/3/2019 16:09	12/2/2019 14:58
0	71	Normal	ME	12/3/2019 8:00	Completed	ME	Data	No	71	12/3/2019 19:23	12/2/2019 16:13
0	72	Normal	AC	12/2/2019 8:00	Completed	AC	Reports	No	72	12/2/2019 19:35	12/2/2019 18:15
0	73	Normal	AC	12/2/2019 8:00	Completed	AC	Reports	No	73	12/2/2019 19:33	12/2/2019 18:55
0	75	Normal	Vacant	12/12/2019 8:00	Completed	AC	Data	No	75	12/12/2019 20:50	12/2/2019 22:23
0	76	Normal	Vacant	12/12/2019 8:00	Completed	AC	Data	No	76	12/12/2019 20:54	12/2/2019 22:50
0	78	Normal	Vacant		Active	Vacant	Reports	No	78	12/3/2019 15:41	12/3/2019 15:41
0	79	Normal	AC	12/3/2019 8:00	Completed	AC	Software	No	79	12/3/2019 19:21	12/3/2019 19:15
0	80	Normal	MS	1/29/2020 8:00	Completed	MS	Reports	No	80	1/30/2020 16:36	12/3/2019 20:17
0	87	Normal	MS		Completed	MS	Software	No	87	1/30/2020 16:35	12/5/2019 17:03
0	89	Normal	AC	12/5/2019 8:00	Completed	AC	Data	No	89	12/5/2019 20:59	12/5/2019 20:35
0	93	Normal	AC	12/12/2019 8:00	Completed	AC	Reports	No	93	2/5/2020 21:58	12/8/2019 16:44
0	95	Normal	AC	12/9/2019 8:00	Completed	AC	Software	No	95	12/9/2019 16:03	12/9/2019 15:43
0	98	Normal	AC	1/22/2020 8:00	Completed	AC	Reports	No	98	1/22/2020 22:58	12/10/2019 15:06
0	103	Normal	AC	12/12/2019 8:00	Completed	AC	Data	No	103	12/12/2019 14:48	12/11/2019 20:14
0	105	Normal	AC	12/12/2019 8:00	Completed	AC	Data	No	105	12/12/2019 20:13	12/12/2019 20:13
0	106	Normal	AC	12/16/2019 8:00	Completed	AC	Data	No	106	12/16/2019 22:45	12/16/2019 21:46
0	108	Normal	ME	12/18/2019 8:00	Completed	ME	Data	No	108	12/18/2019 15:45	12/17/2019 15:10
0	109	Normal	AC	12/17/2019 8:00	Completed	AC	Data	No	109	12/17/2019 18:01	12/17/2019 16:40
0	111	Normal	MS	12/10/2019 8:00	Completed	MS	Data	No	111	2/5/2020 21:51	12/18/2019 15:02
0	112	Normal	AC	12/18/2019 8:00	Completed	AC	Data	No	112	12/18/2019 15:40	12/18/2019 15:22
0	113	Normal	AC	12/18/2019 8:00	Completed	AC	Data	No	113	12/19/2019 17:25	12/18/2019 17:59
0	114	Normal	AC	12/19/2019 8:00	Completed	AC	Reports	No	114	12/19/2019 17:32	12/18/2019 18:22
0	115	Normal	LS	12/19/2019 8:00	Completed	LS	Data	No	115	12/19/2019 17:23	12/18/2019 19:37
0	116	Normal	AC	12/19/2019 8:00	Completed	AC	Software	No	116	12/19/2019 17:18	12/18/2019 19:42
0	117	Normal	BM	12/19/2019 8:00	Completed	BM	Data	No	117	12/19/2019 17:15	12/18/2019 20:31
0	118	Normal	AC	12/19/2019 8:00	Completed	AC	Data	No	118	12/19/2019 17:13	12/18/2019 21:25
0	123	Normal	MS	12/23/2019 8:00	Completed	MS	Data	No	123	2/5/2020 21:46	12/20/2019 21:39
0	124	Normal	KO	12/27/2019 8:00	Completed	KO	Data	No	124	12/27/2019 19:21	12/23/2019 17:05
0	125	Normal	AC	12/23/2019 8:00	Completed	AC	Data	No	125	12/30/2019 16:53	12/23/2019 17:49
0	127	Normal	MS		Completed	MS	Data	No	127	2/4/2020 20:38	12/26/2019 22:11
0	128	Normal	AC	12/23/2019 8:00	Completed	AC	Data	No	128	12/30/2019 16:52	12/30/2019 13:37
0	129	Normal	AC	1/2/2020 8:00	Completed	AC	Data	No	129	1/2/2020 19:06	12/30/2019 18:33
0	130	Normal	AC	12/30/2019 8:00	Completed	AC	Data	No	130	12/30/2019 20:31	12/30/2019 19:15
0	132	Normal	AC	1/2/2020 8:00	Completed	AC	Data	No	132	1/2/2020 19:00	12/30/2019 22:49
0	133	Normal	AC	12/31/2019 8:00	Completed	BM	Data	No	133	12/31/2019 18:55	12/31/2019 13:27
0	134	Normal	AC	12/31/2019 8:00	Completed	BM	Data	No	134	12/31/2019 18:56	12/31/2019 14:48
0	136	Normal	AC	1/2/2020 8:00	Completed	AC	Data	No	136	1/2/2020 18:57	1/2/2020 16:31
0	137	Normal	AC	1/2/2020 8:00	Completed	AC	Data	No	137	1/2/2020 18:58	1/2/2020 16:36
0	138	Normal	AC	1/2/2020 8:00	Completed	AC	Data	No	138	1/2/2020 16:46	1/2/2020 16:38
0	139	Normal	AC	1/2/2020 8:00	Completed	AC	Data	No	139	1/2/2020 19:22	1/2/2020 16:55
0	140	Normal	Vacant		Active	Vacant	Data	No	140	1/3/2020 19:02	1/3/2020 19:02

[CW112358]

Since I can't get my dropbox to work.  Above is a piece of the data (enough to work with) below is all in the charts, but I want a slicer/filter to show last 7 days, 14 days, 30 days, 90 days and Overall.  Thanks in advance!

[edhans]

@CW112358 - see this file.

I put in a 7 and 14 day filter. You can see in Power Query how I did that and how to add other filters for any N day slicer you want. A few issues:

1. You didn't specify which was your date key and you have 3-4 date fields in your table. I based it on AD Date Completed. If that is wrong you'll need to:
   1. fix the varStartDate and varEndDate fields references in Power Query. These will make your date table 100% dynamic.
   2. Change the relationship in the Model view to relate Date[Date] to the proper field.
2. If AD Date Complated is right, it has some blanks, which is why the filters show blanks right now.
3. Your date key really should be just a date field, not date time. You may want to create a new date field that is Date.DateTime([your date field]) and use that for the varStartDate/varEndDate, as well as in the date table relationship if you must retain the times in the other fields.

The reason I do this in Power Query is it is generally more effecient than calculated columns.

In general, try to avoid calculated columns. There are times to use them, but it is rare. Getting data out of the source system, creating columns in Power Query, or DAX Measures are usually preferred to calculated columns. See these references:
Calculated Columns vs Measures in DAX
Calculated Columns and Measures in DAX
Storage differences between calculated columns and calculated tables
Creating a Dynamic Date Table in Power Query

Any questions let me know.

[CW112358]

The field for date is called "Created"

[edhans]

ok. can you make the necessary changes noted above, including getting rid of the time component of the field?

[CW112358]

Changed all dates in all tables to date only, no time.  then checked the relationships again (attached photos).  and there are no blanks in AD_Status, Created.  Still is blank.  Wish I could attach the bi file here.

[edhans]

@CW112358  - see this file. I made the following changes:

1. Added a new column to your data table called DateKey which is the date only based on Created - DateTime.Date([Created])
2. I updated the varStartDate and varEndDate to use this new DateKey field.
3. Saved the tables and loaded into the model.
4. Updated the relationship to link Date[Date] to Table[DateKey] as a one-to-many.

The other things you need to do if trying to replicate this in your model is

1. make sure the Date table is marked as a date table:
   1. 
2. Disable auto-date-time. Ensure it is disabled globally, and for the current files. Disabling globally will disable for new files, but you might have to disable in older files if you add a true date table.
   1. Current File SettingsGlobal Settings

Ping back with any questions.

[CW112358]

Thanks for all the help!  Sorry its taking me so long to get back to you.  I'm still working at work which is crazy with all of this so its been pretty stressful.  I tried to create that new DateKey and I got this error?  

[CW112358]

Figured that out.  K let me do the rest.

[CW112358]

Also, to get the last 30 days, you have to check 7, 14 and 30.

[CW112358]

Will it automatically update?  Since this will be something that is live for people to view?  Also, more and more entries will come in.

[V-pazhen-msft]

@CW112358 

It automatically updates everyday. However, after some tests, I would suggest you to use Between Slicer with the following column, this is a more dynamically way compared with create options of last days of 7,14,30,etc...

LastNdays = 
var a=DATEDIFF([Date],TODAY(),DAY)
Return IF(a <= 0, BLANK(),a)

https://qiuyunus-my.sharepoint.com/:u:/g/personal/paul_qiuyunus_onmicrosoft_com/EWqqgAHocgxMkkMroHt3...

Paul Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[CW112358]

I could do this 100% cause I know how it works, the issue with using a drag slicer is I would have to explain that to higher management.  They understand check boxes and push buttons, they don't get the drag slicer idea so I really need to make this as easy as possible for them.

[edhans]

I have some good ideas on how to make this work and would involve a number of columns in Power Query, which would work great for slicers and filters, but I'd need to see some data to make sure I'm going down the right path. See links below, specifically providing sample data.

How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum