Forum Discussion
Count how many days between 2 given dates and plot it
Dear Community, good morning.
I am creating a report analyzing the shipment in / out date in order to manage warehousing operations and have the below issue:
I have a table containing a data like this, so the cargo ID , the day the cargo arrived at the warehouse and the day that it left the warehouse.
| Cargo ID | Entered Day | Released Date |
| A | 01/01/2021 | 03/01/2021 |
| B | 02/01/2021 | 04/01/2021 |
| C | 01/01/2021 | 02/01/2021 |
What I need to do is create a graph showing the total cargoes by date, so I would neet to change the table to something like this:
| Cargo ID | Day |
| A | 01/01/2021 |
| A | 02/01/2021 |
| A | 03/01/2021 |
| B | 02/01/2021 |
| B | 03/01/2021 |
| B | 04/01/2021 |
| C | 01/01/2021 |
| C | 02/01/2021 |
And plot like below:
What is the best way to do this?
Thank you in advance!
Click here to download a solution
You need to build a calendar table with contagious dates
the create this measure ..
Valid cargos =VAR mydate = SELECTEDVALUE('Calendar'[Date])VAR validrows = FILTER(Cargos, Cargos[Entered Day] <= mydate && Cargos[Released Date] >= mydate)RETURNThen use calendar date in you graph x-axis and the [Valid cargos ] as the Y-axis valueCOUNTROWS(validrows)Thanks for reaching out for help.
I have helped you, now please help me by giving kudos.
Remeber we are unpaid volunteers.
Click the thumbs up and accept as solution button.
One question per ticket please. If you need to extend your request then please raise a new ticket.
You will get a quicker response and each volnteer solver will get the kudos they deserve. Thank you !
3 Replies
speedrampsSuper User
Click here to download a solution
You need to build a calendar table with contagious dates
the create this measure ..
Valid cargos =VAR mydate = SELECTEDVALUE('Calendar'[Date])VAR validrows = FILTER(Cargos, Cargos[Entered Day] <= mydate && Cargos[Released Date] >= mydate)RETURNThen use calendar date in you graph x-axis and the [Valid cargos ] as the Y-axis valueCOUNTROWS(validrows)Thanks for reaching out for help.
I have helped you, now please help me by giving kudos.
Remeber we are unpaid volunteers.
Click the thumbs up and accept as solution button.
One question per ticket please. If you need to extend your request then please raise a new ticket.
You will get a quicker response and each volnteer solver will get the kudos they deserve. Thank you !
- speedramps
Hi agian Pedro
Thanks for accepting the solution 😀
I am a vounteer. Please try ask one question per ticket. That way I get twice the solving score ! 😀😀
Quote speedramps and I will received a notofication, but other solvers may help.
I recommend you turn off auto date integence . Only novices use it.
It you want to be a serious Power Bi developer then build a Calender table, test it once and use it always ...
Do all these tutorials a few times and you will become an expert at YTD, MTD, LY, LM, Quarters ...
You will also need to change the measure if you use Month or Quarters in your X-axis.
Valid cargos =
VAR mindate = MIN('Calendar'[Date])
VAR maxdate = MIN('Calendar'[Date])VAR validrows = FILTER(Cargos, Cargos[Entered Day] <= maxdate && Cargos[Released Date] >= mindate)RETURNThanks for reaching out for help.
I have helped you, now please help me by giving kudos.
Remeber we are unpaid volunteers.
Click the thumbs up and accept as solution button.
One question per ticket please. If you need to extend your request then please raise a new ticket.
You will get a quicker response and each volnteer solver will get the kudos they deserve. Thank you ! 😎