Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

TOTALMTD Dax

Hello!

 

I am using a TOTALMTD calculation to take out monthly values. This is linked with a calender as well in order to get the correct data.

 

=TOTALMTD(Sum(AC[Volume]);calendar table[Date])

 

I was wondering if there is any way to make this show the accumulated data for all the months as well? I would prefer not be forced to make a TOTALYTD as well since I have already so many measures that the report is getting a bit slow. But whenever I select two months at the same time it just shows the figures of one month.

 

Thanks in advance

 

Oscar

 
 
Need Help

7 Replies

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

    I think totalytd or datesyts would be a better idea.

    Or you can try something like this

    YTD Sales = 
 var _max = maxx(allselected('Date'),'Date'[Date])
 var _min = maxx(allselected('Date'),STARTOFYEAR ('Date'[Date]))
 return
 CALCULATE(SUM(Sales[Sales Amount]),filter(all('Date'),'Date'[Date]<=_max && >=_min))

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks for your reply! 

       

      I have created all the measures with TOTALMTD and I would like it to show the YTD value as well (without creating new measures such as TOTALYTD). Is there any way to make the TOTALMTD show the YTD value? Perhaps using the date calendar as a filter on the page?

       

      I would prefer to avoid creating new measures such as TOTALYTD as my report is getting a bit slow.

      • edhans's avatar
        edhans
        Icon for Community Champion rankCommunity Champion
        6 years ago

        You should create the new measures. Measures aren't calculated unless used, and a TotalYTD measure will be faster than any hacked TotalMTD measure to get YTD values as it would involved more filtering.

         

        If your report is getting slow, there are other things to consider. In general, Date Intelligence measures are pretty fast if you have a good model set up and aren't doing any unusual filtering in them. i.e. multiple levels of CALCULATE, FILTER, or worse IF() statements.

  • V-lianl-msft's avatar
    V-lianl-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

     

    As edhans said before, you should consider creating a new measure instead of calculating YTD based on MTD.
    You can refer to these articles for optimization of reporting performance and DAX performance:

    https://www.sqlbi.com/articles/capturing-power-bi-queries-using-dax-studio/ 

    https://maqsoftware.com/expertise/powerbi/dax-best-practices 

     

    Best Regards,
    Liang
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hello,

       

      Thank you for all the replies! I have now created a lot of measures with TOTALMTD and TOTALYTD. However, Is there any way to create a filter on the page that shows all the measures in either MTD or YTD?

       

      Otherwise the only option is to have 2 seperate sheets in the same report, one with MTD values and one with YTD values.

       

      Thanks a lot!