DAX Help

[ak77]

Hi All,

I need help with the DAX formula for the below scenario.. please check

Below is the table i am using and MTD ,QTD and YTD are measures. the formula used for MTD is as below as i want to calculate the numbers by filter by just benchmark_code column

BM_MTD =

CALCULATE(

    [Total BM Return],

    DATESMTD('Date Table'[_Date]),ALLEXCEPT ( Broad_Market_Indices_Data,Broad_Market_Indices_Data[benchmark_code] )

)

Now the user wants to remove the Code Column and calculate all the measures.. when i remove the code column from the Table the  values are getting wrong.is there a way to remove Code Column from the table and still use the same formula as mentioned above? please help 

[Ashish_Mathur]

Hi,

In your measure, replace benchmark_code with benchmark_name.

[ak77]

@Ashish_Mathur , Thanks for reply ...i want to calculate using code as name is changing for a particular code after a certain period

@amitchandak , Thanks for reply, i am trying ur DAX.wil get back to u

Thanks again guys

[amitchandak · Accepted solution]

@ak77 , Try this version

BM_MTD =
CALCULATE(
[Total BM Return],
DATESMTD('Date Table'[_Date]),filter ( all(Broad_Market_Indices_Data),Broad_Market_Indices_Data[benchmark_code] = max(Broad_Market_Indices_Data[benchmark_code]) )
)

and is benchmark_code is same as the code; try to have that in a separate dimension and use that in code

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Date date should be marked as date table and join should single direction

Make sure this code alone is working

BM_MTD =
CALCULATE(
[Total BM Return],
DATESMTD('Date Table'[_Date]) )

[ak77]

Thanks @amitchandak , ur reply worked for me. Thanks