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

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

    Thanks amitchandak , ur reply worked for me. Thanks