Forum Discussion
Resolver IPlot 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 Received | End Date |
| 01/04/2017 | 01/05/2017 |
| 01/04/2017 | 01/06/2017 |
| 01/04/2017 | |
| 01/04/2017 | 01/06/2017 |
| 04/04/2017 | 01/05/2017 |
| 04/04/2017 | 01/08/2017 |
| 04/04/2017 | |
| 01/05/2017 | |
| 01/05/2017 | |
| 01/05/2017 | 01/08/2017 |
| 01/05/2017 | 01/10/2017 |
| Month | Count of Active |
| Apr | 11 |
| May | 9 |
| Jun | 7 |
| Jul | 7 |
| Aug | 5 |
| Sep | 5 |
| Oct | 4 |
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
- fhill
Resident Rockstar
Here's another option too:
0: Add an Index column for counting later:
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)
3. Here's how it shoudl ook:
4: Gragh your data with DateList (Month) as the Axis and DISTINCT COUNT of Index as your Values.
Back2BasicsHi 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.
- fhill
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
- nickchobotar
Skilled Sharer
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] ) ) )- Back2Basics
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.
- nickchobotar
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())
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 )The model will look like this ( you can also hide Active table )
Now you can write the DAX measure in your report view.
Count of Active = DISTINCTCOUNT(Active[Index2])
N -
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])
