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

     

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.