cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Continued Contributor
Continued Contributor

Re: Plot stock level of workload

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

Highlighted
Helper III
Helper III

Re: Plot stock level of workload

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?

Highlighted
Super User I
Super User I

Re: Plot stock level of workload

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

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

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 Solution Authors