Unable to calculate sales for max month

[SandySharma]

Hi Gurus,

I got Stuck in calculating Sales for the max month based on the selected slicer in power BI.

Senario:

1. I have a dimenssion called Report Type in this I have two attributes (Available , Common). 

2. For Available my max date is June-2017 & for Available it is May-2017.

I tried :

1. Created a measure called Max_Date_Competitor= IF(HASONEFILTER(Competitor[ReportType]),MAXX(Competitor,Competitor[TIME_YEAR_MONTH].[Date]))

2. Created another files Max_Sales = 

CALCULATE(SUM(Competitor[GROWTH_YTD_NUM_CY]),FILTER(Competitor,Competitor[TIME_YEAR_MONTH].[Date]=[Max_Date_Competitor]))

But System is returing sum of all sales happened since data available.

Please help me how Can I do the same.

[v-yulgu-msft]

Hi @SandySharma,

Please try this formula:

Max_Sales =
CALCULATE (
    SUM ( Competitor[GROWTH_YTD_NUM_CY] ),
    FILTER (
        Competitor,
        MAX ( Competitor[TIME_YEAR_MONTH].[Date] ) = Competitor[Max_Date_Competitor]
    )
)

Best regards,
Yuliana Gu

[SandySharma · Accepted solution]

Hi Yulgu,

Thanks for your time. I solved this problem by reading DAX function from microfoft side https://msdn.microsoft.com/en-us/library/ee634380.aspx

Max_Sales =
CALCULATE (
    SUM ( Competitor[GROWTH_YTD_NUM_CY] ),
    lastdate(Competitor[Max_Date_Competitor])
    )