Unsure of what visualisation to use to count a date event

[Anonymous]

Hi there,

For a top level management report I need to display a number of assets in our system that are still over 4 years old and Active (missed refresh basically).

I have our CMDB table in Power BI that, for example has the asset code type (AT000030 and AT000031) for Laptops.  I also have the date field which contains the date the item was created as well as a column for the amount of months for refresh.

What I need to do is display a total for Workstations, Laptops, Monitors that are past their refresh date.

I have displayed totals on reports before using the Card visualisation and dragging a number value into it that reacts to date and some other filter options.

With this one though, I am just not sure the best way to assemble the data to a total and then display as a number on the report.

I was thinking of somehow having a column that checked if the  HC_Created_Date + RefreshCycle_Months was less than current date (making it older than 48 months) and writing a 1.  Then I could use the card to display the totals filtering by the asset code.  Just unsure how to structure the column to write the 1 or 0.

Thanks.

[v-stephen-msft · Accepted solution]

Hi @Anonymous ,

Create a calculated column.

ToRefresh = IF(DATE(YEAR([HC Created Date]),MONTH([HC Created Date])+[RefreshCycle_Months],DAY([HC Created Date]))<TODAY(),1,0)

Or, you could create a measure.

ToRefresh measure = IF(DATE(YEAR(MAX('Table'[HC Created Date])),MONTH(MAX('Table'[HC Created Date]))+MAX('Table'[RefreshCycle_Months]),DAY(MAX('Table'[HC Created Date])))<TODAY(),1,0)

You can check more details from here.

Best Regards,

Stephen Tao

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

[Anonymous]

Thanks, I have a few variants of this I am trying now.  Will plug this in also and see what comes out in the wash 🙂

cheers

[amitchandak]

@Anonymous , Can you share sample data and sample output in a table format?

For how many months old, you can have a column in the date table like

Diff from this month = datediff([date], eomonth(today(),-1),month)

Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.

[Anonymous]

In the below example AT000030 and 31 are laptops, AT000026 and 27 are workstations.

The sum of the created date and the refresh months for the laptops and one of the workstations ends up being less than the current date.  If I was writing this as a new column there would be a value of 1 against 3 of the values and 0 against the other.

Then on the report I would use a card and just group by the new column value so it would total all the 1 and filer for the relevant asset codes.

Barcode	Asset Code	Asset	HC Created Date	RefreshCycle_Months
123	AT000030	Dell A	10/10/2015	48
223	AT000031	Dell B	11/10/2015	48
444	AT000026	HP WW	15/01/2019	48
555	AT000027	HP EE	19/03/2016	48

So an extra column in the table as so

Barcode	Asset Code	Asset	HC Created Date	RefreshCycle_Months	ToRefresh
123	AT000030	Dell A	10/10/2015	48	1
223	AT000031	Dell B	11/10/2015	48	1
444	AT000026	HP WW	15/01/2019	48	0
555	AT000027	HP EE	19/03/2016	48	1