Forum Discussion
Date-over-Date calculation dividing two measures breaks relationship (e.g. YoY, MoM, etc)
Hi Anonymous ,
From what I am understanding, here's my solution.
1.Create a calendar. If you have a calendar table yourself, you can use it directly.
Calendar = CALENDAR(MIN('revenue'[date]),MAX('revenue'[date]))The relationships are as follows.
2.Revenue M-1 and Revenue M-2 are as follows.
Revenue M-1 = var _date=SELECTEDVALUE('Calendar'[Date]) var _end=EOMONTH(_date,-1) var _start=EOMONTH(_date,-2)+1 return CALCULATE(SUM(revenue[revenue]),FILTER('revenue',[date]>=_start&&[date]<=_end))Revenue M-2 = var _date=SELECTEDVALUE('Calendar'[Date]) var _end=EOMONTH(_date,-2) var _start=EOMONTH(_date,-3)+1 return CALCULATE(SUM(revenue[revenue]),FILTER('revenue',[date]>=_start&&[date]<=_end))Then you can calculate the MoM.
MoM = DIVIDE([Revenue M-1]-[Revenue M-2],[Revenue M-2])3.The dates in the slicer are from the calendar table. For example, when you select '4/28/2021', the result is as follows.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Here's sample data:
Table with revenue per client, which is also the table where all the measures are being created:
| client_id | date | revenue |
| 1 | 01/01/2021 | 10 |
| 1 | 01/02/2021 | 20 |
| 1 | 01/03/2021 | 30 |
| 1 | 01/04/2021 | 40 |
| 1 | 01/05/2021 | 50 |
| 1 | 01/06/2021 | 60 |
| 2 | 01/01/2021 | 70 |
| 2 | 01/02/2021 | 80 |
| 2 | 01/03/2021 | 90 |
| 2 | 01/04/2021 | 100 |
| 2 | 01/05/2021 | 110 |
| 2 | 01/06/2021 | 120 |
| 3 | 01/01/2021 | 130 |
| 3 | 01/02/2021 | 140 |
| 3 | 01/03/2021 | 150 |
| 3 | 01/04/2021 | 160 |
| 3 | 01/05/2021 | 170 |
| 3 | 01/06/2021 | 180 |
| 4 | 01/01/2021 | 190 |
| 4 | 01/02/2021 | 200 |
| 4 | 01/03/2021 | 210 |
| 4 | 01/04/2021 | 220 |
| 4 | 01/05/2021 | 230 |
| 4 | 01/06/2021 | 240 |
| 5 | 01/01/2021 | 250 |
| 5 | 01/02/2021 | 260 |
| 5 | 01/03/2021 | 270 |
| 5 | 01/04/2021 | 280 |
| 5 | 01/05/2021 | 290 |
| 5 | 01/06/2021 | 300 |
Table with clients and their respective account managers:
| client_id | acc_manager | client_group |
| 1 | John Joe | Strategic |
| 2 | Jane Joe | Strategic |
| 3 | Jack Jay | Corporate |
| 4 | Mary Moore | Corporate |
| 5 | Louis Lato | Medium |
| 6 | Lianna Lisa | Medium |
Here's the actual outcome whenever I add only the absolute revenue values (which is the expected result):
| client_id | acc_manager | client_group | Revenue M-1 | Revenue M-2 |
| 1 | John Joe | Strategic | 30 | 20 |
| 2 | Jane Joe | Strategic | 90 | 80 |
| 3 | Jack Jay | Corporate | 150 | 140 |
| 4 | Mary Moore | Corporate | 210 | 200 |
| 5 | Louis Lato | Medium | 270 | 260 |
| 6 | Lianna Lisa | Medium | 0 | 0 |
Here's the actual outcome whenever I add the MoM variation using client_id from the revenue table:
| client_id | acc_manager | client_group | Revenue M-1 | Revenue M-2 | MoM |
| 1 | John Joe | Strategic | 30 | 20 | 50,0% |
| 1 | Jane Joe | Strategic | 0 | 0 | -100,0% |
| 1 | Jack Jay | Corporate | 0 | 0 | -100,0% |
| 1 | Mary Moore | Corporate | 0 | 0 | -100,0% |
| 1 | Louis Lato | Medium | 0 | 0 | -100,0% |
| 2 | John Joe | Strategic | 0 | 0 | -100,0% |
| 2 | Jane Joe | Strategic | 90 | 80 | 12,5% |
| 2 | Jack Jay | Corporate | 0 | 0 | -100,0% |
| 2 | Mary Moore | Corporate | 0 | 0 | -100,0% |
| 2 | Louis Lato | Medium | 0 | 0 | -100,0% |
| 3 | John Joe | Strategic | 0 | 0 | -100,0% |
| 3 | Jane Joe | Strategic | 0 | 0 | -100,0% |
| 3 | Jack Jay | Corporate | 150 | 140 | 7,1% |
| 3 | Mary Moore | Corporate | 0 | 0 | -100,0% |
| 3 | Louis Lato | Medium | 0 | 0 | -100,0% |
| 4 | John Joe | Strategic | 0 | 0 | -100,0% |
| 4 | Jane Joe | Strategic | 0 | 0 | -100,0% |
| 4 | Jack Jay | Corporate | 0 | 0 | -100,0% |
| 4 | Mary Moore | Corporate | 210 | 200 | 5,0% |
| 4 | Louis Lato | Medium | 0 | 0 | -100,0% |
| 5 | John Joe | Strategic | 0 | 0 | -100,0% |
| 5 | Jane Joe | Strategic | 0 | 0 | -100,0% |
| 5 | Jack Jay | Corporate | 0 | 0 | -100,0% |
| 5 | Mary Moore | Corporate | 0 | 0 | -100,0% |
| 5 | Louis Lato | Medium | 270 | 260 | 3,8% |
Here's the outcome I'm looking for when adding the MoM variations:
| client_id | acc_manager | client_group | Revenue M-1 | Revenue M-2 | MoM |
| 1 | John Joe | Strategic | 30 | 20 | 50,0% |
| 2 | Jane Joe | Strategic | 90 | 80 | 12,5% |
| 3 | Jack Jay | Corporate | 150 | 140 | 7,1% |
| 4 | Mary Moore | Corporate | 210 | 200 | 5,0% |
| 5 | Louis Lato | Medium | 270 | 260 | 3,8% |
| 6 | Lianna Lisa | Medium | 0 | 0 | -100,0% |
Hi Anonymous ,
From what I am understanding, here's my solution.
1.Create a calendar. If you have a calendar table yourself, you can use it directly.
Calendar = CALENDAR(MIN('revenue'[date]),MAX('revenue'[date]))The relationships are as follows.
2.Revenue M-1 and Revenue M-2 are as follows.
Revenue M-1 =
var _date=SELECTEDVALUE('Calendar'[Date])
var _end=EOMONTH(_date,-1)
var _start=EOMONTH(_date,-2)+1
return CALCULATE(SUM(revenue[revenue]),FILTER('revenue',[date]>=_start&&[date]<=_end))Revenue M-2 =
var _date=SELECTEDVALUE('Calendar'[Date])
var _end=EOMONTH(_date,-2)
var _start=EOMONTH(_date,-3)+1
return CALCULATE(SUM(revenue[revenue]),FILTER('revenue',[date]>=_start&&[date]<=_end))Then you can calculate the MoM.
MoM = DIVIDE([Revenue M-1]-[Revenue M-2],[Revenue M-2])
3.The dates in the slicer are from the calendar table. For example, when you select '4/28/2021', the result is as follows.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.