average times a name is in a column per day

[kjpackshootdk]

Hey

I hope someone can help with this.

We are looking for a way to calculate the average times a name is in a column per day in the last rolling month. I have tried all types of count and some few different samples found on this site, but all those are made to take the sum of something, not the count.

[v-eachen-msft]

Hi @kjpackshootdk ,

Here is my test data.

First, open query editor and add an index column.

Then pivot "Column1" and use the count of index as value.

Now, you will get a new table and you could calculate the average times for each name easily.

[YJ]

HI,

throw in some example and it would be easier. prebably in pbix or excel.

Does the match just need to be a full match or partial match is counted.( example app in apple)

regards

[kjpackshootdk]

Hey

Thanks for answering, good idea to attach some data! Some sample data is here: https://we.tl/t-YPGTAQMXqH 

I did try this @v-eachen-msft but it doesn't seem to work when the days are in date format. It just returns a mess of dates.

[Anonymous]

Hello,

Is this what you are trying to achieve based on the data?

Put the name on the axis on in the table and use a measure like this one:

Measure =
CALCULATE ( COUNTROWS ( Ark1 ); Ark1[Change_date] >= ( TODAY () - 30 ) ) / 30

It counts the rows in the past 30 days from today and divide it by 30 to get the average per day.

Is this what you are looking for?

Kind regards
Joren Venema

Data & Analytics Consultant
If this reply solved your question be sure to mark this post as the solution to help others find the answer more easily. 

[kjpackshootdk]

Hey Joren

Thanks for your help!

That is close to what I wanted, but the value doesn't seem to be correct.

The values I get are about 1/3 of what they should be. Can it be that this includes the weekend ?

If you have used all of the sample data I believe there is something wrong. The values you should have had from that should be:

As there is only one day in the sample data, it should just have been the sum.

[Anonymous]

Hello @kjpackshootdk ,

I kinda miss understood your average context.

At the moment the measure calculates based on all the dates in the previous 30 days. And statically devide it by 30 (days in a month). So yes the weekends are included in this.

The base values of the table you provided:

For me Name 2 returns 22 rows.

If I understand you correctly you want calculate how many times a Name is present in the data set and divide it by the amount of days in the previous 30 days of which name has a occurance?

[kjpackshootdk]

Yes that is correct. It should only be the days that the name appears 

[Anonymous · Accepted solution]

Alright try the following code:

Measure =
VAR DaysPresent =
    CALCULATE (
        DISTINCTCOUNT ( Ark1[Date] );
        Ark1[Change_date]
            >= ( TODAY () - 30 )
    )
RETURN
    CALCULATE ( COUNTROWS ( Ark1 ); Ark1[Change_date] >= ( TODAY () - 30 ) ) / DaysPresent

In the table you provided it only has a Date/Timestamp column. To calculate how many days the person is present we need a date field. You can add this column either in the Power Query editor by taking the date  from the timestamp or create a calculated column like the following:

Date = LEFT(Ark1[Change_date];10)

And set the column as date.

Kind regards
Joren Venema

Data & Analytics Consultant
If this reply solved your question be sure to mark this post as the solution to help others find the answer more easily. 

[kjpackshootdk]

Thank you so much! That was just what did it.

I will work a bit with this and will maybe come back with a few extra questions