The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
@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.
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 |
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!
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:
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThe field for date is called "Created"
ok. can you make the necessary changes noted above, including getting rid of the time component of the field?
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingChanged 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.
@CW112358 - see this file. I made the following changes:
The other things you need to do if trying to replicate this in your model is
Ping back with any questions.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThanks 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?
Figured that out. K let me do the rest.
Also, to get the last 30 days, you have to check 7, 14 and 30.
Will it automatically update? Since this will be something that is live for people to view? Also, more and more entries will come in.
@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)
Paul Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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.
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
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingJoin the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the August 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
111 | |
80 | |
78 | |
43 | |
37 |
User | Count |
---|---|
157 | |
112 | |
64 | |
60 | |
54 |