Build an asset availability report

[alex1888]

Hello, 

I have the following requirement that I'm not sure can be realized using Power bi. My data source is dynamics 365, And involved in the data structure 2 relevant entities Assets and Allocation to assets. 

Example of data structure:

Assets

Assets ID	ASSET NAME
1	A1
2	A2
3	A4

Allocation to assets

Assets ID	START DATE	END DATE
1	October 1, 2021	October 1, 2022
2	September 15, 2021	October 30, 2021
2	November 30, 2021	October 1, 2022

The requirement to retrieve assets that are not occupied within the user's date range enters, In the example above the user is looking for a free asset between the dates October 30 to November 30 he is expected to get one result of property number A2.

We can ask the developers of dynamics 365 to add additional fields or data but do you think it can be realized using power bi?

Thank you very much for your help !

[v-kelly-msft · Accepted solution]

Hi  @alex1888 ,

Create a measure as below:

Measure = 
var _mindate=CALCULATE(MIN('calendar table'[Date]),ALLSELECTED('calendar table'))
var _maxdate=CALCULATE(MAX('calendar table'[Date]),ALLSELECTED('calendar table'))
var _tab=CALCULATETABLE(VALUES('Allocation to assets'[Assets ID]),FILTER(ALL('Allocation to assets'),'Allocation to assets'[END DATE]<=_mindate||'Allocation to assets'[START DATE]>=_maxdate))
VAR _names=CALCULATETABLE(VALUES(Assets[ASSET NAME]),FILTER(ALL(Assets),'Assets'[Assets ID] in _tab))
Return
CONCATENATEX(_names,'Assets'[ASSET NAME],",")

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[amitchandak]

@alex1888 , check the file attached. Treat the employee name as asset ID, and Leave date are occupied date. Now check the formula to show 0 for available and 1 for occupied. The asset table will act as one more dimension

[alex1888]

Hi @amitchandak thanks for the reply 😀, I admit I could not figure out the example you gave and the file.

But I may not have been clear - The table Allocation to assets Includes only data when the property IS NOT available I want to ask the opposite question, when the asset is available? Given your advice how to proceed?