Forum Discussion

ak77's avatar
ak77
Post Patron
2 years ago
Solved

DAX Help

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 

Need Help
Show and Tell
  • amitchandak's avatar
    amitchandak
    2 years ago

    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]) )

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    2 years ago

    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's avatar
      ak77
      Post Patron
      2 years ago

      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