Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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])
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.
Proud to 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
Proud to 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
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] ) ) )
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.
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 -
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |