Forum Discussion

CW112358's avatar
CW112358
Icon for Helper II rankHelper II
6 years ago

Count Something in Last N Days

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...
Need Help
Tutorial Requests
Anonymous's avatar
Anonymous
Not applicable
6 years ago

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/EWqqgAHocgxMkkMroHt3AIwBnJKfUw6oB9PIe0rczGpuyw?e=6Khg6t


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

  • CW112358's avatar
    CW112358
    Icon for Helper II rankHelper II
    6 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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/EWqqgAHocgxMkkMroHt3AIwBnJKfUw6oB9PIe0rczGpuyw?e=k4IAma

       

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

      • CW112358's avatar
        CW112358
        Icon for Helper II rankHelper II
        6 years ago

        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.

  • CW112358's avatar
    CW112358
    Icon for Helper II rankHelper II
    6 years ago

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

  • CW112358's avatar
    CW112358
    Icon for Helper II rankHelper II
    6 years ago
    FileSystemObjectTypeIdAD_PriorityAD_Assigned ToAD_Date CompletedAD_StatusAD_Completed BySR_CategoryPending Email SentID.1ModifiedCreated
    033NormalBM11/19/2019 8:00CompletedBMSoftwareNo3311/19/2019 20:3511/13/2019 18:14
    044NormalAC2/5/2020 8:00CompletedACOtherNo442/5/2020 22:0211/18/2019 21:30
    045NormalBM11/19/2019 8:00CompletedBMDataNo4511/19/2019 20:3611/19/2019 14:15
    051NormalMS12/12/2019 8:00CompletedMSDataNo512/4/2020 20:3311/20/2019 14:34
    054NormalAC12/2/2019 8:00CompletedACSoftwareNo5412/2/2019 20:3011/20/2019 17:54
    056NormalME CompletedMEDataNo5611/21/2019 18:3811/20/2019 19:51
    058NormalAC11/21/2019 8:00CompletedACSoftwareNo5811/21/2019 22:1111/21/2019 19:51
    060NormalME11/26/2019 8:00CompletedMEDataNo6012/2/2019 17:4811/25/2019 15:44
    061NormalAC11/25/2019 8:00CompletedACDataNo6111/25/2019 19:3511/25/2019 19:17
    064NormalAC11/27/2019 8:00CompletedACDataNo6411/27/2019 18:2011/27/2019 17:56
    067NormalMS CompletedMSSoftwareNo672/4/2020 20:3512/1/2019 21:18
    069NormalAC12/3/2019 8:00CompletedACDataNo6912/3/2019 16:0912/2/2019 14:58
    071NormalME12/3/2019 8:00CompletedMEDataNo7112/3/2019 19:2312/2/2019 16:13
    072NormalAC12/2/2019 8:00CompletedACReportsNo7212/2/2019 19:3512/2/2019 18:15
    073NormalAC12/2/2019 8:00CompletedACReportsNo7312/2/2019 19:3312/2/2019 18:55
    075NormalVacant12/12/2019 8:00CompletedACDataNo7512/12/2019 20:5012/2/2019 22:23
    076NormalVacant12/12/2019 8:00CompletedACDataNo7612/12/2019 20:5412/2/2019 22:50
    078NormalVacant ActiveVacantReportsNo7812/3/2019 15:4112/3/2019 15:41
    079NormalAC12/3/2019 8:00CompletedACSoftwareNo7912/3/2019 19:2112/3/2019 19:15
    080NormalMS1/29/2020 8:00CompletedMSReportsNo801/30/2020 16:3612/3/2019 20:17
    087NormalMS CompletedMSSoftwareNo871/30/2020 16:3512/5/2019 17:03
    089NormalAC12/5/2019 8:00CompletedACDataNo8912/5/2019 20:5912/5/2019 20:35
    093NormalAC12/12/2019 8:00CompletedACReportsNo932/5/2020 21:5812/8/2019 16:44
    095NormalAC12/9/2019 8:00CompletedACSoftwareNo9512/9/2019 16:0312/9/2019 15:43
    098NormalAC1/22/2020 8:00CompletedACReportsNo981/22/2020 22:5812/10/2019 15:06
    0103NormalAC12/12/2019 8:00CompletedACDataNo10312/12/2019 14:4812/11/2019 20:14
    0105NormalAC12/12/2019 8:00CompletedACDataNo10512/12/2019 20:1312/12/2019 20:13
    0106NormalAC12/16/2019 8:00CompletedACDataNo10612/16/2019 22:4512/16/2019 21:46
    0108NormalME12/18/2019 8:00CompletedMEDataNo10812/18/2019 15:4512/17/2019 15:10
    0109NormalAC12/17/2019 8:00CompletedACDataNo10912/17/2019 18:0112/17/2019 16:40
    0111NormalMS12/10/2019 8:00CompletedMSDataNo1112/5/2020 21:5112/18/2019 15:02
    0112NormalAC12/18/2019 8:00CompletedACDataNo11212/18/2019 15:4012/18/2019 15:22
    0113NormalAC12/18/2019 8:00CompletedACDataNo11312/19/2019 17:2512/18/2019 17:59
    0114NormalAC12/19/2019 8:00CompletedACReportsNo11412/19/2019 17:3212/18/2019 18:22
    0115NormalLS12/19/2019 8:00CompletedLSDataNo11512/19/2019 17:2312/18/2019 19:37
    0116NormalAC12/19/2019 8:00CompletedACSoftwareNo11612/19/2019 17:1812/18/2019 19:42
    0117NormalBM12/19/2019 8:00CompletedBMDataNo11712/19/2019 17:1512/18/2019 20:31
    0118NormalAC12/19/2019 8:00CompletedACDataNo11812/19/2019 17:1312/18/2019 21:25
    0123NormalMS12/23/2019 8:00CompletedMSDataNo1232/5/2020 21:4612/20/2019 21:39
    0124NormalKO12/27/2019 8:00CompletedKODataNo12412/27/2019 19:2112/23/2019 17:05
    0125NormalAC12/23/2019 8:00CompletedACDataNo12512/30/2019 16:5312/23/2019 17:49
    0127NormalMS CompletedMSDataNo1272/4/2020 20:3812/26/2019 22:11
    0128NormalAC12/23/2019 8:00CompletedACDataNo12812/30/2019 16:5212/30/2019 13:37
    0129NormalAC1/2/2020 8:00CompletedACDataNo1291/2/2020 19:0612/30/2019 18:33
    0130NormalAC12/30/2019 8:00CompletedACDataNo13012/30/2019 20:3112/30/2019 19:15
    0132NormalAC1/2/2020 8:00CompletedACDataNo1321/2/2020 19:0012/30/2019 22:49
    0133NormalAC12/31/2019 8:00CompletedBMDataNo13312/31/2019 18:5512/31/2019 13:27
    0134NormalAC12/31/2019 8:00CompletedBMDataNo13412/31/2019 18:5612/31/2019 14:48
    0136NormalAC1/2/2020 8:00CompletedACDataNo1361/2/2020 18:571/2/2020 16:31
    0137NormalAC1/2/2020 8:00CompletedACDataNo1371/2/2020 18:581/2/2020 16:36
    0138NormalAC1/2/2020 8:00CompletedACDataNo1381/2/2020 16:461/2/2020 16:38
    0139NormalAC1/2/2020 8:00CompletedACDataNo1391/2/2020 19:221/2/2020 16:55
    0140NormalVacant ActiveVacantDataNo1401/3/2020 19:021/3/2020 19:02
    • CW112358's avatar
      CW112358
      Icon for Helper II rankHelper II
      6 years ago

      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's avatar
        edhans
        Icon for Community Champion rankCommunity Champion
        6 years ago

        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.