Date slicer for ADLastLogon

[PoshandBI]

I have a table of all ADComputer Objects
Columns include PCName,ADLastLogonDate,ADStatus,ReportDate

The ADStatus could be Active, Disable, Delete depending on ADLastLogonDate

I would like a chart to show how many devices I will be disabling for the next 10 days starting tomorrow.  From my own reading I my need to create a second table with CALENDAR(NOW()-59,NOW()-50) to get the value of dates but I'm not sure where the next step is.  When I try to link the two tables together I get errors.

Thanks

[Anonymous · Accepted solution]

Hi @PoshandBI ,

According to your description, here are my steps you can follow as a solution.

(1) This is my test data.  

(2)We can create a table.

CALENDAR = CALENDAR(TODAY()+1,TODAY()+11)

(3) We can create two measures. 

Disable devices = CALCULATE(COUNTROWS(FILTER(ALL('Table'),'Table'[ADStatus]="Disable" && 'Table'[ADLastLogonDate]>=TODAY()+1 && 'Table'[ADLastLogonDate]<=TODAY()+11)))

Measure 2 = CALCULATE(COUNTROWS(FILTER('Table','Table'[ADStatus]="Disable" && 'Table'[ADLastLogonDate]>=MIN('CALENDAR'[Date]) && 'Table'[ADLastLogonDate]<=MAX('CALENDAR'[Date]))))

(4) Then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

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

[PoshandBI · Accepted solution]

Hi.  This solution is close but it doesn't appear to account for the dates that are 60 days in past 🙂  Here is how the solution I came up with After I Impoted my data, I created a new column named WithinDates - the formula within it

WithinDates =

var DateRange = DATEDIFF('Data ActiveDirectoryLog'[AdLastLogonDate],now(),DAY)

return DateRange

This gives me a new filter on the visual to do less than or eq to 59 and greater than or equal to 50

Apologies for the delay

[Anonymous · Accepted solution]

Hi @PoshandBI ,

According to your description, here are my steps you can follow as a solution.

(1) This is my test data.  

(2)We can create a table.

CALENDAR = CALENDAR(TODAY()+1,TODAY()+11)

(3) We can create two measures. 

Disable devices = CALCULATE(COUNTROWS(FILTER(ALL('Table'),'Table'[ADStatus]="Disable" && 'Table'[ADLastLogonDate]>=TODAY()+1 && 'Table'[ADLastLogonDate]<=TODAY()+11)))

Measure 2 = CALCULATE(COUNTROWS(FILTER('Table','Table'[ADStatus]="Disable" && 'Table'[ADLastLogonDate]>=MIN('CALENDAR'[Date]) && 'Table'[ADLastLogonDate]<=MAX('CALENDAR'[Date]))))

(4) Then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

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