Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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.
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
@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.
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 |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |