Add culumn for a data range

[Anonymous]

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

[AlB]

@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.

[AlB · Accepted solution]

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]

Hi @AlB,

your solution works for may problem.

Below is the product graph

Thank a lot

Nino