Return highest value in a period and the month

[Haydn_R]

I have a dataset which has a variabe called Channel. The values for Channel are:

1. Channel 1
2. Channel 2
3. Channel 3
4. Channel 4 
5. Channel 5 
6. Channel 6

I also have a varuabke called Report_Date (this will be a dates for a rolling 18 month period).

What'd I like is to have is the Channel with the hightest count (variable for count is called "Customer") for the 18 months.

Result will be used in a table , but will be used as text.

So it would look like.

The channel with the highest customer count is Channel 5, with #,###,### and the month with the highest customer count is August 2021

[v-yueyunzh-msft]

Hi , @Haydn_R 

Thanks for your quick response and your provided sample data.

For your sample data , i can not fully understand the logic you need to calculate. 

I also have some questions about it:

1."The rolling 18 months" means the range date you need to calculate? Dose it mean the max date may be 2022/10/20 ,  you need to calculate the date range is the " 2022/10/20" -18 Months ?

2.And the "Highest Customer" , is just the max count in [Customer] column? In your sample data , the value is 47733? And i can not find the way to get "104,614 and 191,496" in your sample data.

3."The channel with the highest customer count" , i am not fully sure this logic. Assume the highest customer count Channel is the "Channel6", how we can to get the The channel with the highest customer count?

Can you patient explain the detailed logic to calculate this number in your sample data?

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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

[Haydn_R]

1."The rolling 18 months" means the range date you need to calculate? Dose it mean the max date may be 2022/10/20 ,  you need to calculate the date range is the " 2022/10/20" -18 Months ?

Sample data is not for 18 months. I just created a small table in excel. Technically the data will only be for 18 months. So at this momment it's from May 2021 to November 2022. When I get new data it will be from June 2021 to December 2022

2.And the "Highest Customer" , is just the max count in [Customer] column? In your sample data , the value is 47733? And i can not find the way to get "104,614 and 191,496" in your sample data.

Summing up Customer count by month, January has 104,614. Summing up by Channel, Channel2 has 191,496 customers.

3."The channel with the highest customer count" , i am not fully sure this logic. Assume the highest customer count Channel is the "Channel6", how we can to get the The channel with the highest customer count?

As above Channel2 has the higest number of customers for the dummy data of 191,496

[v-yueyunzh-msft · Accepted solution]

Hi , @Haydn_R 

Thanks for your quick response and detailed explanation!

Here are the steps you can refer to :
We can create these measures :

Highest Customer = var _t = SUMMARIZE('Table','Table'[Year_month] , "Customer" , SUM('Table'[Customer]))
return
MAXX(_t,[Customer])

Higest Cust Month = var _t = SUMMARIZE('Table','Table'[Year_month] , "Customer" , SUM('Table'[Customer]))
var _max_value =MAXX(_t,[Customer])
var _year_month = MAXX( FILTER(_t, [Customer]=_max_value) , [Year_month])
var _year = QUOTIENT(_year_month,100)
var _month = MOD(_year_month,100)
return
FORMAT(DATE(_year,_month,1) ,"mmmm yyyy")

Highest Cust Channel = var _t = SUMMARIZE('Table','Table'[Channel] , "Customer" , SUM('Table'[Customer]))
return
MAXX(_t,[Customer])

Highest Channel Month = var _t1 = SUMMARIZE('Table','Table'[Channel] , "Customer" , SUM('Table'[Customer]))
var _max_value1= MAXX(_t1,[Customer])
var _max_channel = MAXX( FILTER(_t1 ,[Customer] =_max_value1) , [Channel])
 var _t = SUMMARIZE(FILTER('Table','Table'[Channel]=_max_channel),'Table'[Year_month] , "Customer" , SUM('Table'[Customer]))
var _max_value =MAXX(_t,[Customer])
var _year_month = MAXX( FILTER(_t, [Customer]=_max_value) , [Year_month])
var _year = QUOTIENT(_year_month,100)
var _month = MOD(_year_month,100)
return
FORMAT(DATE(_year,_month,1) ,"mmmm yyyy")

Max Channel = var _t1 = SUMMARIZE('Table','Table'[Channel] , "Customer" , SUM('Table'[Customer]))
var _max_value1= MAXX(_t1,[Customer])
var _max_channel = MAXX( FILTER(_t1 ,[Customer] =_max_value1) , [Channel])
return
_max_channel

Then we can create a text box and enter this measures:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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

[v-yueyunzh-msft]

Hi , @Haydn_R 

According to your description, you want to show the result as a text . I have a question for your need.

"You have a varuabke called Report_Date (this will be a dates for a rolling 18 month period)" , For this Report_Date, what is the data structure in this table ?

Would you like to provide us with some test data and the final results in a form?
This will make the problem clearer and simpler.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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

[Haydn_R]

This is what I have so far:
Customer Max=
var _roll18 = CALCULATE(Count(Table[Customer]),DATESINPERIOD('Date Table'[Date],MAX('Date Table'[Date]),-18,MONTH))
var _topn = maxx(topn(1, allselected(Table[Customer]) ,_roll18, desc),[Customer])
return,
CALCULATE(sumx(values(Date[Month Year]) ,CALCULATE(Count(Table[Customer]))), filter(Table,Table[Customer] = _topn))

[Haydn_R]

Below is dummy data.

My intention is concatenate the new variables and use these as text in a table or card, so it will read like: The customer count is 104,614 this was for the month of January 2022. The channel with the highest customer count of 191,496 is Channel 2. Channel 2 had the highest customer count in Janaury 2022.

Hopefully this gives you an idea what I'm after. 

New Measures

Highest Customer =104,614

Higest Cust Month = January 2022

Highest Channel = Channel2

Highest Cust Channel = 191,496

Highest Channel Month = January 2022

Dummy Data

[Haydn_R]

ok got this to work, well kind of. it's returning a row count rather than a sum of customers. Also it doesnt have the column of "Channel" in here, which is what I also need a highest mumber of by month.


Customer Max=
var _roll18 = CALCULATE(Count(Table[Customer]),DATESINPERIOD('Date Table'[Date],MAX('Date Table'[Date]),-18,MONTH))
var _topn = maxx(topn(1, allselected(Table[Customer]) ,_roll18, desc),[Customer])
return,
CALCULATE(sumx(values(Date[Month Year]) ,CALCULATE(Count(Table[Customer]))), filter(Table,Table[Customer] = _topn))

[HiraNegi]

Hi @Haydn_R ,

You can create measures to calculate the channel and month with highest count, and then use smart narrative to show the required text along with the calculated measure.

Please share some more details/screenshot if this not what you are looking for.

Regards,

Hira Negi

[Haydn_R]

Thanks for your reply. Unfortunately Smart Narrative is not available to me. Our company has not enable this feature.

[amitchandak]

@Haydn_R , Try a measure with help from date table joined to report date

var _roll18 =CALCULATE(Count(Table[Customers]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-18,MONTH))
var _topn = maxx(topn(1, allselected(Table[Customers]) ,_roll18, desc),[Csutomer])
return
,CALCULATE(sumx(values(Date[Month Year]) ,CALCULATE(Count(Table[Customers]))), filter(Table,Table[Customer] = _topn))

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.