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
Anonymous
Not applicable

Highest Monthly Total in A Given Time Period - Measure

Hello,

 

I am trying to create a measure which can show the highest monthly total sales in a given time period. See the sales table below:

 

DateSales
7/2/2020 $        100
7/2/2020 $        200
7/28/2020 $        500
8/4/2020 $        300
9/5/2020 $        200
7/2/2021 $        100
7/2/2021 $        100
8/4/2021 $        900
9/5/2021 $        200

 

There is another date table that I want to filter on. When I filter year 2020, the measure should show $800 (July 2021 total sales amount); when I filter 2021, the measure should show $900 (August 2021 total sales amount); when I don't filter anything, it should show $900 (August 2021 total sales amount); when I select a sepecific month, it should show the monthly total sales amount of the month selected - e.g. if I select Sept 2021, it should show $200. 

 

Is there a way to create this measure without creating a new table?

 

Thank you very much for your help!

1 ACCEPTED SOLUTION
CNENFRNL
Community Champion
Community Champion

Screenshot 2021-08-15 233035.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

Thank you both very much for your help! @CNENFRNL @Jos_Woolley 

CNENFRNL
Community Champion
Community Champion

Screenshot 2021-08-15 233035.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

Jos_Woolley
Solution Sage
Solution Sage

Hi,

Assuming your Sales table is related appropriately to a Date table named Dates:

Max Monthly Sales =
VAR MyTable =
    SUMMARIZE (
        Sales,
        Dates[Date].[Year],
        Dates[Date].[Month],
        "Monthly Sales", SUM ( Sales[Sales] )
    )
RETURN
    MAXX ( MyTable, [Monthly Sales] )

Regards

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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