Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello!
I am having a lot of trouble at creating a visualization that show the expected free capacity of some storages at the present day and for each following day (up to 5).
I have a Table as shown below, with a number of requests to occupy the storage space and an expected end date for that occupation.
Order Nº | Storage | Volume (Kg) | End Date |
1 | 1 | 10 | 25/04/2021 |
2 | 1 | 10 | 26/04/2021 |
3 | 1 | 80 | 30/04/2021 |
4 | 2 | 100 | 27/04/2021 |
5 | 2 | 100 | 30/04/2021 |
Using this information, I need to create this table as a visualization (no problem from my side generating the occupancy % for each Storage). My lack of knowledge is struggling with the expected occupancy for the next days:
Today | Tomorrow | n+2 | |
Storage 1 | 100% | 90% | 80% |
Storage 2 | 100% | 100% | 50% |
Storage 3 | 0% | 100% | 80% |
Please, do you have any ideas on how I could create this?
Thank you so much!!
Regards,
Hi @Rate ,
It is not clear to your question. What is the calculation logic of the created occupancy %? Does the storage amount update dynamically with the date? Hope to provide detailed requirements and test data (delete sensitive information) so that I can answer you as soon as possible.
Looking forward to your reply.
Best Regards,
Henry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Rate , Not very clear, you can try like
measure with help from date table
today = CALCULATE([occupancy %], FILTER(ALL('Date'),'Date'[Date]=today() ))
tomorrow = CALCULATE([occupancy %], FILTER(ALL('Date'),'Date'[Date]=today() + 1))
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
Hello @v-henryk-mstf ,
Thank you very much for your answer, I have prepared a .pbix file with the two tables I mentioned before, hoping it gives you more information regarding my problem. I am not allowed to attach files, so I hope a Drive link is useful!
https://drive.google.com/file/d/1Jj-31_Dn-qzr2lLcUkv2IMB_eRb7XsAm/view?usp=sharing
As per your questions,
- What is the calculation logic of the created occupancy %? I have included measures describing this question.
- Does the storage amount update dynamically with the date? - Max. Storage amount is fixed for each storage unit (as you can see in the table "Storage Detail". What is changing daily with the date is the amount of storage used (as per table Storage Use[Tn], in the range of time between Real Start Date and Planned End Date).
Again, thank you so much for your time and let me know if you need any further clarification.
Thanks!