Forum Discussion
Helper II% Change over Month by customer
Hey All,
I have a table that essentially looks like below. I want to make a slicer for the app column that sees % change of all customers that roll up to that application. In order to do this, I need it to be in the source table.
| Account Number | Account Name | Revenue | Month Date | Application |
| 123 | ACME | 123 | 1/1/2023 | App 1 |
| 123 | ACME | 1235 | 1/1/2023 | App 2 |
| 123 | ACME | 653 | 2/1/2023 | App 1 |
| 123 | ACME | 5634 | 3/1/2023 | App 1 |
| 123 | ACME | 5643 | 4/1/2023 | App 1 |
| 123 | ACME | 5765 | 4/1/2023 | App 2 |
| 123 | ACME | 65855 | 5/1/2023 | App 2 |
| 321 | 23 | 1/1/2023 | App 1 | |
| 321 | 23 | 1/1/2023 | App 2 | |
| 321 | 4343 | 2/1/2023 | App 2 | |
| 321 | 434 | 3/1/2023 | App 2 | |
| 321 | 564 | 4/1/2023 | App 2 |
Every quick measure or dateadd based measure I have tried does not work for this.
Thanks,
Hi Sut_Datanaut ,
I created a sample pbix file(see the attachment), please check if that is what you want.
% change of revenue = VAR _account = SELECTEDVALUE ( 'Table'[Account Name] ) VAR _part = CALCULATE ( SUM ( 'Table'[Revenue] ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Account Name] = _account && 'Table'[Month Date] <= SELECTEDVALUE ( 'Table'[Month Date] ) ) ) VAR _all = CALCULATE ( SUM ( 'Table'[Revenue] ), ALLSELECTED ( 'Table' ) ) RETURN DIVIDE ( _part, _all )Best Regards
3 Replies
- amitchandak
Super User
Sut_Datanaut , Join the date of your table with the date of the date table
and try measures like
Month behind Sales = CALCULATE(Count(Table[Account Name]),dateadd('Date'[Date],-1,month))
MTD = CALCULATE(Count(Table[Account Name]),DATESMTD('Date'[Date]))
last MTD = CALCULATE(Count(Table[Account Name]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))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
Sut_Datanautdoes it have to reference another date table? Or can I use the dates on the fact table.
Also, I want to get % change of revenue, not count of customers.
Thanks
Hi Sut_Datanaut ,
I created a sample pbix file(see the attachment), please check if that is what you want.
% change of revenue = VAR _account = SELECTEDVALUE ( 'Table'[Account Name] ) VAR _part = CALCULATE ( SUM ( 'Table'[Revenue] ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Account Name] = _account && 'Table'[Month Date] <= SELECTEDVALUE ( 'Table'[Month Date] ) ) ) VAR _all = CALCULATE ( SUM ( 'Table'[Revenue] ), ALLSELECTED ( 'Table' ) ) RETURN DIVIDE ( _part, _all )Best Regards
