need to add up number of times the current month total is greater than the prior month total

[bsheffer]

for each account I need to add up the number of times the current month sales totals is greater than the prior month total for a filtered period on the page

Seems like summarize should do it but I think I have to build a summary table in the measure as the table to use in the summarize statement.  Any ideas how to best do this?

[v-xiaotang]

Hi @bsheffer 

In fact, your solution is not common. A more general approach is to create a table,

I find several models, you can try this way, (This way is more efficient)

https://community.powerbi.com/t5/Desktop/Calendar-Table/m-p/1860460

or if you can share your sample data or sample file without sensitive information, then I can write specified measure for your sample.

Best Regards,

Community Support Team _Tang

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

[bsheffer]

here is some sample data

merchant_number	total_sales_amount	activity_month
1	150	6/1/2021
1	100	5/1/2021
1	50	4/1/2021
2	50	6/1/2021
2	40	5/1/2021
2	100	4/1/2021
3	100	5/1/2021
3	50	4/1/2021
4	50	6/1/2021

a measure that uses this data should show the result in a table of merchant_number and months_improved

merchant_number	months_improved
1	2
2	1
3	0
4	0

6/1/2021 is the current month.  Merchant 3 should have a value of 0 because it doesn't have activity in the current month.  Merchant 4 should have 0 because it only has 1 month of activity.

[v-xiaotang · Accepted solution]

Hi @bsheffer 

you can try this measure

Measure = 
var _currentMonth=CALCULATE(MAX('Table'[activity_month]),ALL('Table'))
var _sales=CALCULATE(MIN('Table'[total_sales_amount]),FILTER(ALLEXCEPT('Table','Table'[merchant_number]),'Table'[activity_month]=_currentMonth))
var _count=IF(_sales=0,0,CALCULATE(COUNTROWS('Table'),FILTER(ALL('Table'),'Table'[merchant_number]=MIN('Table'[merchant_number])&&'Table'[activity_month]<_currentMonth&&'Table'[total_sales_amount]<_sales)))
return IF(ISBLANK(_count),0,_count)

result

Best Regards,

Community Support Team _Tang

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

[bsheffer]

I was able to complete it by brute force but I think there has to be a more elegant solution.  Here is the code I wrote.  I tested back 8 months but really the maximum number of months improved has been 4

_months_improved =

var _month = max('Fact Cubes'[ACTIVITY_MONTH])

var _monthdates = DATESINPERIOD('Dim Calendar ActivityMonth'[Date], _month, 1, MONTH)

var _monthimproved0 = if(calculate([_TOTAL_SALES_VOLUME_AMEX], all('Dim Calendar ActivityMonth'), DATEADD(_monthdates, 0, MONTH)) - calculate([_TOTAL_SALES_VOLUME_AMEX], all('Dim Calendar ActivityMonth'), DATEADD(_monthdates, -1, MONTH))> 0, 1, 0)

var _monthimproved1 = if(calculate([_TOTAL_SALES_VOLUME_AMEX], all('Dim Calendar ActivityMonth'), DATEADD(_monthdates, -1, MONTH)) - calculate([_TOTAL_SALES_VOLUME_AMEX], all('Dim Calendar ActivityMonth'), DATEADD(_monthdates, -2, MONTH))> 0, 1, 0)

var _monthimproved2 = if(calculate([_TOTAL_SALES_VOLUME_AMEX], all('Dim Calendar ActivityMonth'), DATEADD(_monthdates, -2, MONTH)) - calculate([_TOTAL_SALES_VOLUME_AMEX], all('Dim Calendar ActivityMonth'), DATEADD(_monthdates, -3, MONTH))> 0, 1, 0)

var _monthimproved3 = if(calculate([_TOTAL_SALES_VOLUME_AMEX], all('Dim Calendar ActivityMonth'), DATEADD(_monthdates, -3, MONTH)) - calculate([_TOTAL_SALES_VOLUME_AMEX], all('Dim Calendar ActivityMonth'), DATEADD(_monthdates, -4, MONTH))> 0, 1, 0)

var _monthimproved4 = if(calculate([_TOTAL_SALES_VOLUME_AMEX], all('Dim Calendar ActivityMonth'), DATEADD(_monthdates, -4, MONTH)) - calculate([_TOTAL_SALES_VOLUME_AMEX], all('Dim Calendar ActivityMonth'), DATEADD(_monthdates, -5, MONTH))> 0, 1, 0)

var _monthimproved5 = if(calculate([_TOTAL_SALES_VOLUME_AMEX], all('Dim Calendar ActivityMonth'), DATEADD(_monthdates, -5, MONTH)) - calculate([_TOTAL_SALES_VOLUME_AMEX], all('Dim Calendar ActivityMonth'), DATEADD(_monthdates, -6, MONTH))> 0, 1, 0)

var _monthimproved6 = if(calculate([_TOTAL_SALES_VOLUME_AMEX], all('Dim Calendar ActivityMonth'), DATEADD(_monthdates, -6, MONTH)) - calculate([_TOTAL_SALES_VOLUME_AMEX], all('Dim Calendar ActivityMonth'), DATEADD(_monthdates, -7, MONTH))> 0, 1, 0)

var _monthimproved7 = if(calculate([_TOTAL_SALES_VOLUME_AMEX], all('Dim Calendar ActivityMonth'), DATEADD(_monthdates, -7, MONTH)) - calculate([_TOTAL_SALES_VOLUME_AMEX], all('Dim Calendar ActivityMonth'), DATEADD(_monthdates, -8, MONTH))> 0, 1, 0)

return

if(_monthimproved0 + _monthimproved1 + _monthimproved2 + _monthimproved3 + _monthimproved4 + _monthimproved5 + _monthimproved6 + _monthimproved7 = 8, 8,

if(_monthimproved0 + _monthimproved1 + _monthimproved2 + _monthimproved3 + _monthimproved4 + _monthimproved5 + _monthimproved6 = 7, 7,

if(_monthimproved0 + _monthimproved1 + _monthimproved2 + _monthimproved3 + _monthimproved4 + _monthimproved5 = 6, 6,

if(_monthimproved0 + _monthimproved1 + _monthimproved2 + _monthimproved3 + _monthimproved4 = 5, 5,

if(_monthimproved0 + _monthimproved1 + _monthimproved2 + _monthimproved3 = 4, 4,

if(_monthimproved0 + _monthimproved1 + _monthimproved2 = 3, 3,

if(_monthimproved0 + _monthimproved1 = 2, 2,

if(_monthimproved0 = 1, 1,

0)

)

)

)

)

)

)

)

I'm using it in a table with account, count of improved months for a 2 year period w sorting on improved months from highest to lowest.

[v-xiaotang]

Hi @bsheffer 

this requirement seems to be possible without building a summary table. But for details, I need to look at your sample data. could you share your sample data or sample file after removing sensitive data?

Any question, please let me know. looking forward to receiving your reply.

Best Regards,

Community Support Team _Tang

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