Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
Anonymous
Not applicable

Automatically calculate the average of the number of rows of a dataset every month

I have a dataset that shows a snapshot of the current employees in the company, which means there is no timestamp. Anytime I open the dataset, it shows the employees at the moment. Is it possible to have Power BI automatically calculate the averaged number of employees in the company on the 1st of every month?

 

If one row represents one employee, the total number of rows will be the number of employees. On the 1st of every month, I would like Power BI to count the number of rows and average them. For instance, on Jan 1st, Power BI might get 11 rows from the snapshot of the dataset. On Feb 1st, Power BI might get 20 rows from the sanpshot of the dataset. I would like Power BI to average 11 and 20, and give me 16 as the result in Feb. By mar 1st, if we get 15 rows, I would like Power BI to average 11, 20, and 16, and give me 16 in Mar.

 

How do I do that?

1 ACCEPTED SOLUTION
Anonymous
Not applicable
4 REPLIES 4
v-xicai
Community Support
Community Support

Hi @Anonymous ,

 

You may create calculated column to get [Year_Month], then create measure to get Average like DAX below.

 

Column:

Year_Month= FORMAT('Date'[Date],"yyyymm")


Measure :

Average Count =
VAR _Table =
    SUMMARIZE (
        Table1,
        Table1[Year_Mont],
        "Count", CALCULATE ( COUNT ( Table1[EmployID] ) )
    )
RETURN
    AVERAGEX ( _Table, [Count] )

Best Regards,

Amy 

 

Community Support Team _ Amy

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

pranit828
Community Champion
Community Champion

Hi @Anonymous 

 

If your dataset always gets the data of all employes for each month(data has to be there in daatset to calculate average)

I would use 

 

AVG_Count_Per_month =
AVERAGEX (
    SUMMARIZE (
        Table1,
        Table1[month-year],
        Table1[id],
        "Count", COUNT ( Table1[id] )
    ),
    [Count]
)

 

Did I resolve your issue? Mark my post as a solution! Appreciate your Kudos, Press the thumbs up button!!

 

Regards,
Pranit





PBI_SuperUser_Rank@1x.png


Hope it resolves your issue? 
Did I answer your question? Mark my post as a solution!

Appreciate your Kudos, Press the thumbs up button!!
Linkedin Profile
Anonymous
Not applicable

I don't have timestamp in my dataset. Here is a couple of examples of my dataset.

 

In Jan, my Power BI will load this dataset. Total number of employee = 2

Employee CodeHired dateEmployee Status
a1232010/07/01A
a1252015/05/01A


In Feb, my Power BI will load this dataset, and the dataset I showed above (in Jan) will be OVERWRITTEN by this one. Total number of employee = 2.

Employee CodeHired dateEmployee Status
a1232010/07/01A
a1252015/05/01I
a1272020/02/01A

 

In Mar, my Power BI will load this dataset, and the dataset I showed above (in Feb) will be OVERWRITTEN by this one. Total number of employee = 3.

Employee CodeHired dateEmployee Status
a1232010/07/01A
a1252015/05/01I
a1272020/02/01A
a1292020/03/05A

 

Any suggestions?

Anonymous
Not applicable

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.