cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
Anonymous
Not applicable

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])

 

 

Super User I
Super User I

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!




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.

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!




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

Continued Contributor
Continued Contributor

@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

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.

@Back2Basics

 

Date format should not make any difference. If you want to show active duration I would recommend to use SQLBI pattern for this

https://www.sqlbi.com/articles/analyzing-events-with-a-duration-in-dax/

 

It also appears you cannot add an index field in your power query, so this solution has to be a pure DAX one.

Basically, we need to create a separate table with continuous daily duration of your activity,  set an unique index and take a distinct count in our DAX measure.

 

Here is the link for the model

https://1drv.ms/u/s!AsgNvkRwqGC7gwrpwX-L7WBuS5y9

 

 

Since you cannot work in Power Query let's try to create your index column in DAX

Index1 = 
CALCULATE(
    DISTINCTCOUNT(Table1[Dated Received]),
    FILTER(
        Table1,
        Table1[Dated Received] <= EARLIER(Table1[Dated Received])
    )
)
    
    
    
    

 

It's very important index values are unique, if you have similar Dates Received I would suggest you bring additional field into the model to create unique Index along with current field (like a composite unique key) or create the index on the data source side.

 

Once Index is created, let's create second Index only for Active values

Index2 = 
IF(ISBLANK(Table1[End Date]), Table1[Index1], BLANK())

 

image.png

 

 

Now let's create our transformed table. In my sample model I called it Active

 

 

Active = 
FILTER (
    SELECTCOLUMNS (
        GENERATE (
            Table1,
            FILTER (
                ALLNOBLANKROW ( 'DimDate' ),
                DimDate[Date] >= Table1[Dated Received]
                    && DimDate[Date] <= TODAY ()
            )
        ),
        "Date", [Date],
        "Index2", [Index2]
    ),
    [Index2] > 0
)

 

image.png

 

The model will look like this ( you can also hide Active table )

 

 

image.png

 

Now you can write the DAX measure in your report view.

 

Count of Active = DISTINCTCOUNT(Active[Index2])

 

 

image.png

 

 

N -

Thanks you so much for the response @nickchobotar

 

I have finally had chance to try it out, but unfortunately it hasn't quite worked for me. All of the columns and measures appears to go through without a problem but when I create the graph as suggested I just get each month with the same number of 'active'.

 

Also, lookint at your example i'm not sure it is really showing what I am after. what I would like to see if the number of applications that WERE active in January. what I think your graph shows is the number of applications received in January that are still active.

So your graph should display 7 for Janaury and 12 for February [4 received none ended] but then 15 for March [4 received by 1 ended]

 

Thank you again, but not sure i'm going to achieve what I want to without adding in loads of columns. I was thining I could add a column for each month and to calculate the 'active' based on the dates, maybe I can do this as a sepeteate table though?

So if I'm reading your post right, you're wanting for, say, January, anything that has a received date in January or earlier, but at that time no end date? If that's the case, then it's equivalent to something having either no end date, or an end date in February or later, so you could try making calculated columns that converts both dates into year-month format (i.e. this month is 201710), converts your blanks in the end date to some large number, do the same on your date table, then do some calculation where for anything in your date table, it counts the number of rows where the received date is <= the date table value, but the end date is > the date table value

Anonymous
Not applicable

@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.

 

 

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
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors