Cumulative Counts

[Anonymous]

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 )

[Anonymous]

Hi @Anonymous 

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]

@Anonymous , 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