Forum Discussion

vishwanathans's avatar
vishwanathans
Icon for Helper II rankHelper II
5 years ago

DATEADD error - Help me how to fix

Hi Experts,

 

Can somebody be able to guide me how to fix this issue,

 

 

My dax code : 

Last MTD Cost = CALCULATE(SUM('Financial Data'[Cost]),DATESMTD(DATEADD('Financial Data'[Period name],-1,MONTH)))
 
Thanks in Advance.
 
Srinivasan Vishwanathan

 

Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    vishwanathans , I all such cases you should use a date table, Joined with date of your table

     

    Last MTD Cost = CALCULATE(SUM('Financial Data'[Cost]),DATESMTD(DATEADD('Date'[Date],-1,MONTH)))

     

    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

     

    Please provide your feedback comments and advice for new videos
    Tutorial Series Dax Vs SQL Direct Query PBI Tips
    Appreciate your Kudos.

    • vishwanathans's avatar
      vishwanathans
      Icon for Helper II rankHelper II
      5 years ago

      amitchandak  Hi, I tried this dax code. But Last MTD is not calculating properly. Last MTD value computing entire cost column.

       Can you please help me to sort this out.

       

      Thanks for your help.

       

      Thanks

      Srini

      • v-jingzhang's avatar
        v-jingzhang
        Icon for Community Support rankCommunity Support
        5 years ago

        Hi vishwanathans 

         

        What visualization do you put the measure in? Do you have any slicer or filter or something like rows/columns in a table/matrix to let the measure know what date should be used to calculate the value? Can you please provide some sample data or sample pbix as well as the expected result so that I can understand the error more clearly? Remember to remove the sensive information in the sample file. Thanks.

         

        Best regards,

        Jing