Measure Not Correct

[NJ81858]

Hello,

I am trying to create a 3-month rolling average of the percentage of sales that are at a physical location instead of online. This is achieved by summing the dollars for the last 3 months when the location is not blank, and dividing that by the total sum of the dollars for the last 3 months. The issue that I am having is that it is summing the total sales correctly, but when I add the condition that the location is not blank, it only sums for the most recent month.

Here is my measure for the numerator of my calculation:

_num = 

VAR _end = LASTDATE('Table'[Date])
VAR _start = FIRSTDATE(DATESINPERIOD('Table'[Date], _end, -3, MONTH))

RETURN CALCULATE(SUM([Sales]), DATESBETWEEN('Table'[Date], _start, _end), FILTER('Table', [Location] <> BLANK()))

Here is my measure for the denominator of my calculation:

_denom = 

VAR _end = LASTDATE('Table'[Date])
VAR _start = FIRSTDATE(DATESINPERIOD('Table'[Date], _end, -3, MONTH))

RETURN CALCULATE(SUM([Sales]), DATESBETWEEN('Table'[Date], _start, _end))

Here are the tables of the sales for the last 3 months, the left table has the filter that the Location is not blank, and the right table is everything.

Note that the totals at the bottom are what should be returned by my two measures.

When I calculate these measures for the end of March, these are the values that I get:

From these results, the denominator is calculating correctly, but the numerator is ignoring the dates and only calculating for the most recent month, even though the only thing that is different is the addition of the condition that the location is not blank. Any help would be greatly appreciated!

[Anonymous · Accepted solution]

Hi @NJ81858 ,

Please try this measure:

_num = 

VAR _end = LASTDATE('Table'[Date])
VAR _start = FIRSTDATE(DATESINPERIOD('Table'[Date], _end, -3, MONTH))

RETURN CALCULATE(SUM([Sales]), DATESBETWEEN('Table'[Date], _start, _end), FILTER(ALL('Table'), [Location] <> BLANK()))

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[NJ81858]

@Anonymous This worked thank you!