Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
Hi @Anonymous ,
I think you are talking about running total. If so, you could reference my simple sample to have a try.
Date = CALENDAR(MIN('Table'[Date]),MAX('Table'[Date]))
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.
Hi @Anonymous ,
I think you are talking about running total. If so, you could reference my simple sample to have a try.
Date = CALENDAR(MIN('Table'[Date]),MAX('Table'[Date]))
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.
@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/
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
98 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |