cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

Plot stock level of workload

Hi

 

I have a table with 'Date received' and 'End Date' columns.

 

An item is 'Active' when there is a date received but no end date. Once an end date has been entered it si no longer active.

 

What I would like to do is plot a graph to show how many active rows there are over time. So the data would look something like this:

 

Date ReceivedEnd Date
01/04/201701/05/2017
01/04/201701/06/2017
01/04/2017 
01/04/201701/06/2017
04/04/201701/05/2017
04/04/201701/08/2017
04/04/2017 
01/05/2017 
01/05/2017 
01/05/201701/08/2017
01/05/2017

01/10/2017

 

MonthCount of Active
Apr11
May9
Jun7
Jul7
Aug5
Sep5
Oct4

 

I can do this fr the current place in time, but I am struggling to make it work to plot a graph or to find the stock levels on any given date.

12 REPLIES 12
Highlighted
Continued Contributor
Continued Contributor

Re: Plot stock level of workload

@Back2Basics

 

I would add a calculated column to your table and create a month value

 

Month = MONTH(Table1[Date Received])

 

and for logic I would write something like this.

 

Count of Active =
CALCULATE (
    COUNTROWS ( Table1 ),
    FILTER (
        Table1,
        NOT ISBLANK ( Table1[Date Received] ) && ISBLANK ( Table1[End Date] )
    )
)

 

image.png

 

 

image.png

Highlighted
Super User I
Super User I

Re: Plot stock level of workload

Here's another option too:  

 

0:  Add an Index column for counting later:

Capture2.PNG

 

1. Add a new Custom Column in Query Editor to Insert all the dates between DateReceived to (EndDate or TODAY if EndDate is blank)

 

{Number.From([DateReceived])..Number.From(
if [EndDate] is null
then Date.From(DateTime.LocalNow())
else [EndDate]
)}

 

2.  Expand the list to show all dates between your Date Received and Endate (or Today).  Format the new data as Dates

     (Index column was cut off in the screen shot)

Capture.PNG

 

3. Here's how it shoudl ook:

 Capture3.PNG

 

 4:   Gragh your data with DateList (Month) as the Axis and DISTINCT COUNT of Index as your Values.

Capture5.PNG



Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


Proud to be a Super User helping give back to the community!
Thank You!




Highlighted
Anonymous
Not applicable

Re: Plot stock level of workload

Instead of creating a calculated column, I would create a column in Query Editor; it will compress much better.

 

Go to Add Column in QE's ribbon, and Add Conditional Column

 

if [End Date] equals null then 0 else 1

 

Click OK.

 

the formula bar will now show this:

 

Table.AddColumn(<the step immediately before>, "Is Active", each if [End Date] = null then "0" else "1")

 

 

 

Remove the ""s around the 0 and the 1, like this:

Table.AddColumn(<the step immediately before>, "Is Active", each if [End Date] = null then 0 else 1)

Now you can create a measure

[Number of Active] = SUM(Table[Is Active])

 

 

Highlighted
Helper III
Helper III

Re: Plot stock level of workload

Hi @fhill

 

I have tried to do as you suggested but I can't get there. I have used the following expression:

 

{Number.From([nrw_datereceived])..Number.From(if[End Date] is null then Date.From(DateTime.LocalNow())else [End Date])}

 

as suggested, but every time I add it i'm asked to put in credentials. I do that as I normally would to refresh my data, but it gets to the end and then says that I don't have permission.

 

Not sure what I'm doing wrong here? I can't even view the table, once I've added the expression for the column, to expand it.

Highlighted
Helper III
Helper III

Re: Plot stock level of workload

Sorry @nickchobotar but this doesn't work. The date format you've used is the america format, i've been using European (DD/MM/YYY) and I think it would show why it didn't work if that was fixed with what you've done.

 

It does provide the active rows for the current moment in time, but it doesn't work for long term viewing how many where 'active' during each month.

 

@Anonymous a similar problem with your solution as well. It would only give me the active rows on today. I wouldn't be able to use this to plot previous months total active numbers.

Highlighted
Super User I
Super User I

Re: Plot stock level of workload

Are you able to insert the index column, or does that fail as well?  Sounds like you may need a more DAX driven solution than Power Query....  FOrrest



Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


Proud to be a Super User helping give back to the community!
Thank You!




Highlighted
Anonymous
Not applicable

Re: Plot stock level of workload

@Back2Basics

 

Correct, the SUM(Table[Is Active]) will just give you active rows on today.  But if you put Calendar[Month] column in your visual, you should get back all of the products that were active for the month.

 

 

Highlighted
Helper III
Helper III

Re: Plot stock level of workload

I could add the index column okay, it worked but I ended up taking it out as I have another column with a enique identifier so figured I could sue that.

 

@fhillYour sign off completely threw me... as that is also my surname Smiley LOL

Highlighted
Helper III
Helper III

Re: Plot stock level of workload

Sorry @Anonymous, how do I add a calendar month to a visual?

Not quite thinking straight as this has been driving me slightly insane

Helpful resources

Announcements
Ignite

Microsoft Ignite

This will be a conference that you do not want to miss!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Don't miss the Power BI Dev Camp this week!

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Kudoed Authors