Forum Discussion
Asset Availability vs Date
Hi
I am relatively new to Power BI and have come accross a problem I'm struggling to work through and wondered if anyone had any ideas how to resolve.
I am trying to calculate a moving annual total showing downtime to various assets. I have created a seperate date table, and am using a SQL data source, a table called 'aseqdn' which shows each asset, its out of service date, its return to service date amongst other fields. There can be multiple assets out of service at any one time, also there can be assets out of service with no return date.
I have added a column with this formula to calculate the days out of service which seesm to work ok.
Days out of commision = IF(ISBLANK(aseqdn[rts_date]),
DATEDIFF(aseqdn[out_date], TODAY(), DAY),
DATEDIFF(aseqdn[out_date], aseqdn[rts_date], DAY))
I am trying to achieve a list of all dates from year 2000 to date, with a count of assets out of service for each day. Is this possible?
Please let me know if any more info required and thanks in advance for any help!
Regards,
Neil
HI nmoses,
I'd like to suggest you refer to below link to expand your date range columns(start date/end date) to detail date (each day between date range columns).
After you create relationships between expanded table, original table and calendar table , you can direct use calendar date with expanded records to create visual with each day records graph.
Reference link:
Convert date ranges into list of dates?
Regards,
Xiaoxin sheng
1 Reply
HI nmoses,
I'd like to suggest you refer to below link to expand your date range columns(start date/end date) to detail date (each day between date range columns).
After you create relationships between expanded table, original table and calendar table , you can direct use calendar date with expanded records to create visual with each day records graph.
Reference link:
Convert date ranges into list of dates?
Regards,
Xiaoxin sheng