Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Josiel1989
Regular Visitor

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

Hi! community,

I have a dimCalendar like this:

dimCalendar
DateMonth-Year
1/1/2023Jan-23
1/2/2023Jan-23
12/31/2024Dec-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-YearSell AmountMaximum Sell across all Month-Year
Jan-233090
Oct-239090
Feb-246090

 

Thanks!!!

1 ACCEPTED SOLUTION
Ahmedx
Super User
Super User

16 REPLIES 16
Josiel1989
Regular Visitor

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
Super User
Super User

write like this

Screenshot_1.png

Thanks for your help!!!

It works.

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

Thanks.

v-rongtiep-msft
Community Support
Community Support

Hi all, 

Thanks for the quick response.

Hi @Josiel1989 ,

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

vrongtiepmsft_0-1716272637519.png

 

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.

 

 

 

 

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.

pls try this

Screenshot_1.png

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
Super User
Super User

Hi,

Write this measure

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

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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 

Hi,

PBI file attached.

Hope this helps.

Ashish_Mathur_0-1716693903802.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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:

Josiel1989_0-1716761882845.png

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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
Regular Visitor

Hi! thanks for your help,

My fctSells is something like this:

Josiel1989_0-1716142068601.png

 

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

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

AnalyticPulse
Impactful Individual
Impactful Individual

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

 https://analyticpulse.blogspot.com

Power BI : getting started

Dax functions

powerbi Visualisation

 

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.