Forum Discussion

nli's avatar
nli
New Member
7 years ago

Count datestamps between two columns

Hi all,

I am looking for hints of the following problem: I have a list of items which can have a datestamp. They show when the item arrived, when the processing started and when it was finalized.

 

Now I want to see, how many per timeperiod are in each category. For the category "income" that means: Count all items that have a date for incoming and subtract all items which have a date for work or final. Work are all that have a date in work minus whose with final-date. This should be possible to display per year, month, week etc.

 

In order to do so, I wanted to create a second table as a "calendar" starting with the oldest date in my list until today. For each day I would count the dates for each category. Thus for category income the count is "all_dates_in_income - all items that have a date in work or final" (it may be that an item has a final date, but no working date).

I created a relationship for the column income to Date and managed to get the count for income. Any other count however always results in circular dependency.

 

Any help is apriciated :)

 

Datestamp Table:

ID;income;work;final
1001;01.02.2019;09.02.2019;01.04.2019
1002;01.03.2019;01.04.2019;01.05.2019
1003;01.03.2019;15.05.2019;
1004;;;
1005;01.05.2019;15.05.2019;
1006;;;
1007;01.04.2019;15.05.2019;
1008;01.06.2019;;01.07.2019
1009;01.06.2019;;
1010;01.06.2019;;

my Calender Table:

Cal = CALENDAR(DATE(2019;01;01);TODAY())

my Count for incoming:

Count_Inc = CALCULATE(COUNTROWS(Datestamps);ALL('Cal'[Date]))

 

Need Help

3 Replies

  • v-eachen-msft's avatar
    v-eachen-msft
    Community Support
    7 years ago

    Hi nli ,

     

    You could create two new measures to get the counts of income and work.

    countincome =
COUNTROWS ( FILTER ( Datestamp, Datestamp[income] <> BLANK () ) )
    - COUNTROWS ( FILTER ( Datestamp, Datestamp[work] <> BLANK () ) )
    - COUNTROWS (
        FILTER ( Datestamp, Datestamp[final] <> BLANK () && Datestamp[work] = BLANK () )
    )

    countwork =
COUNTROWS ( FILTER ( Datestamp, Datestamp[work] <> BLANK () ) )
    - COUNTROWS (
        FILTER (
            Datestamp,
            Datestamp[final] <> BLANK ()
                && Datestamp[work] <> BLANK ()
        )
    )

    Best Regards,

    Eads

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • nli's avatar
      nli
      New Member
      7 years ago

       

       

      Dear v-eachen-msft 

      thank you very much for your input. To me, your solution cannot refelct the temproal evaluation, instead is a count of the current situation?

      I share some screenshots with my tables:

       

       

      I thought it would be good to have the count in a Calendar to be able so see the temporal evaluation of the current count. If I'd have the same logic as in my "count-inc", I could create a new table which substracts the columns to get the current workload of a timeperiod.

       

      Thanks again!

      Edit:

      A simple CALCULATE() around your formula gives my a calculation in my Calendar-table. However the numbers are not what I expect right now. But I think this can be fixed, I'll give an update.

      This is by the way the result I expect:

      • nli's avatar
        nli
        New Member
        6 years ago

        In Pseudo-Code I would say:

        For all elements in datestamps[income]:
  Find Date in Cal[date]
  increment Cal[countincome]
done

        And the very same thing with work an final.

        Then, I could do the subtraction as a new column..