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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Bharathi_99
Helper IV
Helper IV

Cumulative Counts

Hi all,

I have a table called 'Monthly Active Files', It contains M_KEY, FIRST_DAY_OF_MONTH these two columns in table
The date column in the table only will have first date of each month 
I need to show total active files per each year, it's months and dates
I also need to show average files also

If 2019 year have total M_KEY Counts for the months a below:
Jan : 5, Feb : 10, Mar: 15, Apr:20, May: 25
Then avg for 2019 Months should be like
Jan= 5/month number= 5/1=5
For FEB (5+10)/2=7.5 , For March (5+10+15)/3=10
Like this it goes on
If the year starts from April
then avg should start with APR:5-----Avg= 5/1, For may:10-----avg= 5+10 / 2

Please give me logic for this

Below is the logic I implemented but not giving correct counts

MATTER KEY COUNT= COUNT(WL MATTER EXTRACT[MATTER_KEY]

Cumulative Count Active = CALCULATE ( [MATTER KEY COUNT], DATESYTD ( 'WL Monthly Active Matter Lists'[FIRST_DAY_OF_MONTH] ) )

Cumulative Month Count Active = CALCULATE ( COUNTROWS ( CALCULATETABLE ( VALUES ( 'WL Monthly Active Matter Lists'[Month Year] ), 'WL Matter Extract' ) ), DATESYTD ( 'WL Monthly Active Matter Lists'[FIRST_DAY_OF_MONTH] ) )

Average Active date = VAR _Count = [MATTER KEY COUNT] VAR _YTDCount = [Cumulative Count Active] VAR _Months = [Cumulative Month Count Active] RETURN DIVIDE ( _Count, _Count ) * DIVIDE ( _YTDCount, _Months )

2 REPLIES 2
v-zhengdxu-msft
Community Support
Community Support

Hi @Bharathi_99 

 

Thanks for the reply from @amitchandak.

 

Have you solved your problem? If so, can you share your solution here and mark the correct answer as a standard answer to help other members find it faster? Thank you very much for your kind cooperation! If not, could you please provide more raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following links to share the required info:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

 

Best Regards

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

amitchandak
Super User
Super User

@Bharathi_99 , first of all you should join date of date of your table and try a measure like. Make sure you have date and month year column in date Table

 

CALCULATE (Averagex(Values('Date'[Month Year]), [MATTER KEY COUNT]), DATESYTD ( Date'[Date] ) )

 

Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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