Forum Discussion

Back2Basics's avatar
Back2Basics
Icon for Resolver I rankResolver I
8 years ago

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.   ...
Need Help
nickchobotar's avatar
nickchobotar
Icon for Skilled Sharer rankSkilled Sharer
8 years ago

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

 

 

 

  • Back2Basics's avatar
    Back2Basics
    Icon for Resolver I rankResolver I
    8 years ago

    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's avatar
      nickchobotar
      Icon for Skilled Sharer rankSkilled Sharer
      8 years ago

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

       

       

       

      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 -

      • Back2Basics's avatar
        Back2Basics
        Icon for Resolver I rankResolver I
        8 years ago

        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?

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      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.

       

       

      • Back2Basics's avatar
        Back2Basics
        Icon for Resolver I rankResolver I
        8 years ago

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

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