Best month, worst month

[ContabilidadBI]

Hi guys,

I am trying to calculate a measure that would give me the sales amount for the worst month and another one for the best month (in terms of sales). I am trying to do it with MAX and MIN fuctions but I am not achieving what I want. I would like to put those measures as limits in a Tachometer visualization. Any help please? the sales table I am using has a date column related to a Calendar Table.

Thanks for all the help and learning I am getting from this forum (still a newbie in Power BI).

Sorry if my english is not very good.

Thanks,

[v-huizhn-msft]

Hi @ContabilidadBI,

As I tested, you can create a calculated column using the formula. 

Sale_Amount_month = CALCULATE(SUM(Table1[Sale]),ALLEXCEPT(Table1,Table1[Month]))

Then create measures to get the sales amount for the worst month and for the best month.

Best = CALCULATE(MAX(Table1[Sale_Amount_month]),ALL(Table1))

Worst = CALCULATE(MIN(Table1[Sale_Amount_month]),ALL(Table1))

Please see the following screenshot, the table visual shows the months, and sales amount in each month.



In your report, you use Tachometer customer visual, how do you display the best/worst months' sales amount? 

Best Regards,
Angelia

[ContabilidadBI]

Thanks for your answer Angelina.

I didnt have time yet to try your method, but seems perfectly valid. Is there any way to calculate the calculated column as a mesuare?

In the tachometer visual, the worst month would be the Range 2 start value and the best month, the Range 3 start value. And  sales of current month would be the Value. This is another doubt I have, is there any time inteligence function that filters for current month? something like CURRENTMONTH(), I couldn't find it.

Thank you so much! I will text next week when I try it with the actual data.

[v-huizhn-msft]

Hi @ContabilidadBI,

>>I didnt have time yet to try your method, but seems perfectly valid. Is there any way to calculate the calculated column as a mesuare?

If we can use the sample formula to create [Sale_Amount_month] measure, but we are unable add measure in MAX or Min function.

>>This is another doubt I have, is there any time inteligence function that filters for current month? something like CURRENTMONTH(). 

Based on my solution, you can create a measure using the formula.

month_best=CALCULATE(MAX(Month),Filter(Table1,Table1[Sale_Amount_month]=Table1[Best]))

month_worst=CALCULATE(MAX(Month),Filter(Table1,Table1[Sale_Amount_month]=Table1[Worst]))

Thanks,
Angelia