Forum Discussion

alex1888's avatar
alex1888
New Member
5 years ago
Solved

Build an asset availability report

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 Allocatio...
General Comment
Interesting Links
Need Help
Tips and Tricks
Tutorial Requests
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    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!

  • alex1888's avatar
    alex1888
    New Member
    5 years ago

    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?