Measure to compute the maximum sells across all "Month-Year"

[Josiel1989]

Hi! community,

I have a dimCalendar like this:

dimCalendar
Date	Month-Year
1/1/2023	Jan-23
1/2/2023	Jan-23
…	…
12/31/2024	Dec-24

Also, I have a fctSells table with columns like "Order Date" and "Amount" linked to the dimCalendar. I have created the next measure: [Sell Amount] = SUM(fctSells[Amount]).

Now, my target is to add a new measure that shows the maximum sell across all Month-Year. Something like this:

Month-Year	Sell Amount	Maximum Sell across all Month-Year
Jan-23	30	90
Oct-23	90	90
Feb-24	60	90

Thanks!!!

[Josiel1989]

Hi! I am zorry for the late reply. You dax formula does not work.

I have attached an example file in the next link:

https://drive.google.com/drive/folders/1WzMVN0Pc3969w2yzzzDXivl3kfRyGKLZ 

[Ahmedx · Accepted solution]

write like this

[Josiel1989]

Thanks for your help!!!

It works.

Could you tell me please why it's necessary to include dimCalendar[YearMonth] in the function ALL?

Thanks.

[Ahmedx]

refer to the following

https://www.sqlbi.com/articles/side-effects-in-dax-of-the-sort-by-column-setting/

[Anonymous]

Hi all, 

Thanks for the quick response.

Hi @Josiel1989 ,

I  have created a simple sample, please refer to my pbix file.

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

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

[Josiel1989]

I'm so zorry for the late reply. I have attached an example file in the nextt link.

https://drive.google.com/drive/folders/1WzMVN0Pc3969w2yzzzDXivl3kfRyGKLZ 

My model includes a dimCalendar table.

[Ahmedx]

pls try this

[Josiel1989]

Thanks for your help,

Your method works but a new column "Sales" not related to the dates is being added to the dimCalendar. I would like to maintain the dimCalendar neat.

[Ashish_Mathur]

Hi,

Write this measure

Measure = MAXX(ALL(Calendar[Year-Month]),[Sell amount])

Hope this helps.

[Josiel1989]

I am sorry for the late reply. I tried your method but for some reason the dax formula does not return the expected result. I have attached an example file in the next link:

https://drive.google.com/drive/folders/1WzMVN0Pc3969w2yzzzDXivl3kfRyGKLZ 

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[Josiel1989]

Thanks for your help,

It is very strange because when I use your dax measure in the file that I uploaded in google drive,  the next result is shown:

[Ashish_Mathur]

You are welcome..  Check how i built the Calendar table.

[Josiel1989]

The thing is that I need the calendar table as shown in the sample file that I uploaded because the column 'YearMonth' sorts column 'Month-Year' not only in the matrix visualization but other visualizations in the original file.

[Josiel1989]

Hi! thanks for your help,

My fctSells is something like this:

Also I have created the next measure: [Sell Amount] = SUM(fctSells[Amount])

I have used your dax formula and I doesn't work.

[AnalyticPulse]

hi @Josiel1989 

you can use the below dax and let me know if it helps:
Max Sell across all Month-Year =
VAR MaxSell = CALCULATE(MAX([Sell Amount]), ALL(dimCalendar[Month-Year]))
RETURN
MaxSell

Blog: AnalyticPulse 
Youtube Channel: AnalyticPulse

Power BI : getting started

Dax functions

powerbi Visualisation