Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Add culumn for a data range

Hi all,

 

this is my first Power BI lab test.

 

I have a table that contains the date of the next password change. I would like to extract all dates for password changes in the next 7, 15 and 30 days.

I don't know which is the best method, for example add a column with the number of days (7, 15, 30) or something else. I would like to create a pie chart indicating the number of passwords to change that fall within the three periods (1-7 - 8-15, 16-30). 

 

- All lines with expiration before today's date must be excluded
- All lines with expiration beyond 30 days must be excluded

 

 

 

Thanks for those who want to give me some suggestions.

 

Nino

Need Help
  • AlB's avatar
    AlB
    5 years ago

    Hi Anonymous 

    See the attached file for a possible solution.

    You need to create an ancillary table and a measure:

     

    Password change = 
VAR currentPeriod_ = SELECTEDVALUE(PeriodT[Period])
VAR today_ = TODAY()
RETURN
SWITCH(currentPeriod_,
       7, COUNTROWS(FILTER(Table1, DATEDIFF(today_, Table1[Next Password],DAY) <= 7)),
       15, COUNTROWS(FILTER(Table1, DATEDIFF(today_, Table1[Next Password],DAY) > 7 && DATEDIFF(today_, Table1[Next Password],DAY) <= 15)),
       30, COUNTROWS(FILTER(Table1, DATEDIFF(today_, Table1[Next Password],DAY) > 15 && DATEDIFF(today_, Table1[Next Password],DAY) <= 30))
)

     

     

    		 Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

     

3 Replies

  • AlB's avatar
    AlB
    Community Champion
    5 years ago

    Hi Anonymous 

    See the attached file for a possible solution.

    You need to create an ancillary table and a measure:

     

    Password change = 
VAR currentPeriod_ = SELECTEDVALUE(PeriodT[Period])
VAR today_ = TODAY()
RETURN
SWITCH(currentPeriod_,
       7, COUNTROWS(FILTER(Table1, DATEDIFF(today_, Table1[Next Password],DAY) <= 7)),
       15, COUNTROWS(FILTER(Table1, DATEDIFF(today_, Table1[Next Password],DAY) > 7 && DATEDIFF(today_, Table1[Next Password],DAY) <= 15)),
       30, COUNTROWS(FILTER(Table1, DATEDIFF(today_, Table1[Next Password],DAY) > 15 && DATEDIFF(today_, Table1[Next Password],DAY) <= 30))
)

     

     

    		 Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi AlB,

       

      your solution works for may problem.

       

      Below is the product graph

       

       

      Thank a lot

      Nino

       

  • AlB's avatar
    AlB
    Community Champion
    5 years ago

    Anonymous 

    I had forgotten to attach the file before. Have you seen it? Download and you'll see the steps there

     

    		 Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.