Date-over-Date calculation dividing two measures breaks relationship (e.g. YoY, MoM, etc)

[Anonymous]

I have spent about 2 hours searching around trying to find a solution, but I couldn't find anyone having the same issue and for which the solution solved my problem.

The closest topics I've found where:

https://community.powerbi.com/t5/Desktop/Adding-a-simple-constant-valued-measure-stops-breaks/m-p/22...

https://community.powerbi.com/t5/Desktop/YoY-Change-Quick-Measure-Breaks-When-Filtered/m-p/1090040

First one created the constant in another table with no relationship to the previous two, and I've created all my measures inside one of my tables.

The second one seemed to have a problem with DATEADD, not with the relationship itself.

Now, to my dashboad and my issue:

I have two tables. The first one, 'product_data', has the revenue for each client. The other table, 'clients' has the account manager for each client and how we group them by company size.

'product_data' comes from an API

'clients' is filled manually. We get client_id from the 'product_data' report and Account Managers fill in their names for their clients.

People in my dashboard will select a "before" date in a slicer using 'dax_calendar' and that will define which date is the last one to do the calculations with. 'dax_calendar' is a calendar I created with DAX in this dashboard.

All the measures are being created in my 'product_data' table.

Revenue M-1 (#) = 
var shiftedDate = LASTDATE(DATEADD(dax_calendar[Date], -1, MONTH))
var shiftedMonth = MONTH(shiftedDate)
var shiftedYear = YEAR(shiftedDate)
return
CALCULATE(SUM(product_data[revenue]),
    MONTH(product_data[date]) = {shiftedMonth}, YEAR(product_data[date]) = {shiftedYear})

Revenue M-2 (#) = 
var shiftedDate = LASTDATE(DATEADD(dax_calendar[Date], -2, MONTH))
var shiftedMonth = MONTH(shiftedDate)
var shiftedYear = YEAR(shiftedDate)
return
CALCULATE(SUM(product_data[revenue]),
    MONTH(product_data[date]) = {shiftedMonth}, YEAR(product_data[date]) = {shiftedYear})

Example: if someone selects 2021-05-20 in the slicer, it'll calculate the revenue for March and April.

I also have other measures that calculate a to-date revenue, that's why I opted for using a "before" slicer and getting the last date selected there.

When I add a table using client_id from 'product_data' and these two measures I mentioned above, everything works fine.

But whenever I add this last measure to my table, it breaks the relationship, and each client_id in 'product_data' seems to get related to every client_id in 'clients'.

MoM Revenue (%) = DIVIDE(product_data[Revenue M-1 ($)], product_data[Revenue M-2 ($)],2)-1

Here's the visualization of what happens.

The last thing is: I want to add a filter to the whole dashboard using the 'group'. But because of all these duplications, every single client_id in my measure becomes a part of all groups, under all account managers and the filter becomes useless.

Things I've tried and/or realized:

1. If it makes any difference, I created the relationship manually in a One:Many setup (from 'clients' to 'product_data'), but the relationship gets inverted to Many:One by Power BI automatically (from 'product_data' to 'clients')
2. I first thought maybe DIVIDE was dividing empty values and because it has an alternate value built-in, it was going through. I've removed the DIVIDE and wrote the math directly but it didn't change anything.
3. Filtering for "revenue > 0" doesn't work. Account Managers can select different dates and one thing I want to have explicit in the dashboard are the clients who have zero revenue at some point.
4. Using the column client_id from the clients table instead of from 'product_data' solves the issue, but the thing is: the clients table is delayed compared to product_data because we fill it manually, so I want to show the ID coming from the table product_data.
5. I've been searching for a way to only do the division and show values when both client_id's are the same in both tables, but found nothing.

[Anonymous]

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%

[Anonymous · Accepted solution]

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.

[lbendlin]

Your samples lead me to assume that this is filtered to April 2021?

[lbendlin]

Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to assist you. Avoid posting screenshots of your source data if possible.

Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523