Count of total data over time

[Anonymous]

Hey everyone,

I’m currently developing a dashboard for the alumni association of a university and have some difficulties. Hope you can help me with it. Would appreciate it!

What the alumni club wants is an overview of the membership fluctuation. For every month they want to see how many members are signed in. When a member cancels the subscription, the alumni club deletes the account.

What I basically need is count formula that counts the total members every month and stores the count of the previous month.

f.e.

 in March the total count was 5000

now in April 50 people joined, so for this month I want 5050 in the graph

Both Values should now be displayed in a bar chart.

I have no Idea how I can compute a count formula over time. Does anyone have an idea of how this would work out?

Thank you so much in advance

Best

[v-xuding-msft · Accepted solution]

Hi @Anonymous ,

I think you are talking about running total. If so, you could reference my simple sample to have a try.

• Create a calendar table

Date = CALENDAR(MIN('Table'[Date]),MAX('Table'[Date]))

• Create a measure

Measure =
CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER ( ALL ( 'Date' ), 'Date'[Date] <= MAX ( 'Date'[Date] ) )
)

For more details, please see the attachment. You also could reference the blog: Cumulative Total.  If this is not what you want, please share some sample data and your expected results, we will understand clearly.

Best Regards,

Xue Ding

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

[amitchandak]

@Anonymous m You can use time intelligence to calculate last month. But how would you plan to take care of the deleted account?

With a Date calendar table

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
last year MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))


last QTR same Month (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,Qtr))))


MTD (Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR('Date'[Date])))
MTD (Last Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR(dateadd('Date'[Date],-12,MONTH),"8/31")))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

[Greg_Deckler]

Perhaps incremental refresh. Also, Open Tickets: https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/td-p/409364

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490