Forum Discussion
Week over Week Diff
Hi,
I have a question on a calculation for week-over-week change. I have the data as below:
Column 1 is the Week Ending Date
| Date | Name | Origin Date | Destination Date |
| 6/30/2025 | P1 | 6/30/2025 | |
| 6/30/2025 | P2 | 7/1/2025 | |
| 6/30/2025 | P3 | 6/5/2025 | |
| 6/30/2025 | P4 | 5/15/2025 | |
| 6/30/2025 | P5 | 6/12/2025 | |
| 7/7/2025 | P1 | 6/30/2025 | 7/1/2025 |
| 7/7/2025 | P2 | 7/1/2025 | |
| 7/7/2025 | P3 | 6/5/2025 | |
| 7/7/2025 | P4 | 5/15/2025 | 7/1/2025 |
| 7/7/2025 | P5 | 6/12/2025 | |
| 7/14/2025 | P1 | 6/30/2025 | 7/1/2025 |
| 7/14/2025 | P2 | 7/1/2025 | |
| 7/14/2025 | P3 | 6/5/2025 | 7/2/2025 |
| 7/14/2025 | P4 | 5/15/2025 | 7/1/2025 |
| 7/14/2025 | P5 | 6/12/2025 | |
| 7/14/2025 | P6 | 7/3/2025 | |
| 7/21/2025 | P1 | 6/30/2025 | 7/1/2025 |
| 7/21/2025 | P2 | 7/1/2025 | |
| 7/21/2025 | P3 | 6/5/2025 | 7/2/2025 |
| 7/21/2025 | P4 | 5/15/2025 | 7/1/2025 |
| 7/21/2025 | P5 | 6/12/2025 | 7/4/2025 |
| 7/21/2025 | P6 | 7/3/2025 | |
| 7/21/2025 | P7 | 7/4/2025 |
I want a generate a matrix for:
1. The count of Name that have a Destination Date week-over-week by Week Ending Date
2. The count of Name that have gotten added over previous week, like P7 got added to the week of 7/21 but was not in the 7/14.
Here is the result I need:
| Date | Count of Name | Total Count minus Count of Name with Destination Date | Count of New "Name" - Week over Week |
| 6/30/2025 | 5 | 5 - 0 = 5 | 0 |
| 7/7/2025 | 5 | 5 - 2 = 3 | 0 |
| 7/14/2025 | 6 | 6 - 3 = 3 | 1 (P6) |
| 7/21/2025 | 7 | 7 - 4 = 3 | 1 (P7) |
Thanks in advance.
you can try to create three measures
Count of NameTotal = COUNTROWS('Table')Count minus Count of Name with Destination Date = [Count of NameTotal] - CALCULATE(COUNTROWS('Table'),FILTER('Table',not(ISBLANK('Table'[Destination Date]))))Measure =VAR _date = MAX('Table'[Date])VAR _list = CALCULATETABLE(VALUES('Table'[Name]),'Table'[Date] = _date)VAR _list2 = CALCULATETABLE(VALUES('Table'[Name]),FILTER(ALL('Table'),'Table'[Date] < _date))RETURN if (_date=CALCULATE(min('Table'[Date]),all('Table')),0,countrows(EXCEPT(_list, _list2))+0)pls see the attachment below
pls see the attachment below
6 Replies
you can try to create three measures
Count of NameTotal = COUNTROWS('Table')Count minus Count of Name with Destination Date = [Count of NameTotal] - CALCULATE(COUNTROWS('Table'),FILTER('Table',not(ISBLANK('Table'[Destination Date]))))Measure =VAR _date = MAX('Table'[Date])VAR _list = CALCULATETABLE(VALUES('Table'[Name]),'Table'[Date] = _date)VAR _list2 = CALCULATETABLE(VALUES('Table'[Name]),FILTER(ALL('Table'),'Table'[Date] < _date))RETURN if (_date=CALCULATE(min('Table'[Date]),all('Table')),0,countrows(EXCEPT(_list, _list2))+0)pls see the attachment below
pls see the attachment below
Hi nlakshmi you have 2 requirements, this thiese two measure and let me know your progress.
Names_With_Destination_Count = VAR Result = CALCULATE( DISTINCTCOUNT('Table'[Name]), FILTER('Table', NOT(ISBLANK('Table'[Destination Date]))) ) RETURN IF(ISBLANK(Result), 0, Result)Names_With_Destination_Previous_Week = VAR CurrentWeek = MAX('Table'[Date]) VAR PreviousWeek = CurrentWeek - 7 var result = CALCULATE( DISTINCTCOUNT('Table'[Name]), 'Table'[Date] = PreviousWeek, NOT(ISBLANK('Table'[Destination Date])) ) RETURN IF(ISBLANK(Result), 0, Result)Output:
Find this helpful? ✔ Give a Kudo • Mark as Solution – help others too!
Hi nlakshmi ,
Thank you Royel and ryan_mayu for the response provided!
Has your issue been resolved? If the response provided by the community member addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.
Thank you for your understanding!
