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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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
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.