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 Allocation to assets. 

 

Example of data structure:

Assets

Assets IDASSET NAME
1A1
2A2
3A4

 

Allocation to assets

Assets IDSTART DATEEND DATE
1October 1, 2021October 1, 2022
2September 15, 2021October 30, 2021
2November 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 !

 

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!

3 Replies

    • 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?

  • v-kelly-msft's avatar
    v-kelly-msft
    Community Support
    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!