Measure is not referencing another measure correctly

[Anonymous]

Hi everyone,

I am new to these forums and I am coming across an issue that I have not seen referenced elsewhere, so I thought I would bring it here.  Essentially, I have a line chart that shows the performance of different products for certain metrics on a "months since" basis so that they can be compared based on time since inception rather than on linear time.  Below is a line chart for default rates, which is a simple formula (default dollars / accounts receivable dollars):

Here is my issue.  I have a few metrics where instead of taking AR as my denominator, I am taking average AR over a rolling two month period which is defined by the following measure:

Average_AR = CALCULATE(SUM(FactMonthly[AR]),DATESINPERIOD(FactMonthly[Report_Date],LASTDATE(FactMonthly[Report_Date]),-2,MONTH))/2

Now, here's what happens if instead of dividing defaults by AR, I instead divide it by average AR:

From what I can tell, not only is the default rate incorrect when dividing by average AR, the rate is simply double the amount in the top chart.  I'm really unsure of what could be causing this.  To give more background on my data model, I have a many-to-one relationship between my fact table and my date table:

My date table looks like this:

And additionally, I have an unconnected "Months Since Start" table which I iterate over with the following measure:

DQ Rate (Months Since) =

var FirstMonthNo =

MIN(FactMonthly[Month Number])

var LastMonthNo =

MAX(FactMonthly[Month Number])

var MonthNo = SELECTEDVALUE(DimMonthsSinceStart[Month])

return CALCULATE([Default Rate],

FILTER(ALL(DimDate),DimDate[MonthNoOverYears] = FirstMonthNo + MonthNo))

This measure works great for any rate that I need to calculate where the denominator is a column (like AR), but it screws up whenever the denominator is a measure (average AR).  Any help would be sincerely appreciated as I'm stuck on this problem and have not really found much in the way of similar issues on here.

Thank you

[Anonymous]

Hi Evelyn.  I unfortunately was not able to solve the problem in PowerBI itself due to time constraints.  What I ended up doing was to create a smaller table using Pandas, performing a groupby function and then using the .rolling() function to create an actual column for Total Average AR.  Not an optimal solution but this was the only way I was able to solve this in the given timeframe.  Thank you.

[v-eqin-msft]

Hi @Anonymous ,

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,
Eyelyn Qin

[v-eqin-msft]

Hi @Anonymous ,

It's a little hard to follow.

According to your description, you could use AVERAGEX() or SUMX() or other functions to calculate Measure like: SUMX( FILTER ( TABLE, <CONDITION>), [Measure] ) 

Please provide me with more details about your table and your problem/expected output or share me with your pbix file from your Onedrive for Business WITHOUT sensitive data.

Best Regards,
Eyelyn Qin