Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Sum in quarter

Hi everybody! It's my first time writing here :-) I would really appreciate your help to solve this issue. I have a table which contains these values:   DAYS   | Count of number of runs | Cumu...
Need Help
parry2k's avatar
parry2k
Icon for Super User rankSuper User
7 years ago

Anonymous  do you have calendar/date dimension in your model, it is best practice to have one for time intelligence calculation like yours. you can always use TOTALQTD DAX function to get total by quarter.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi!

    I have both in my Experiment Data and Installed Base data a column with days, which is related to the list.

    By using the TOTALQTD I was not able to obtain the right value.

    • parry2k's avatar
      parry2k
      Icon for Super User rankSuper User
      7 years ago

      Anonymous  hmmm not sure why, if possible, please share data (trim down version) and remove any sensitive information to get the solution.

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        These are all the formulas that I created:

         

        Days table:

        List of days from Jul-2016 to today -> DATE format

        Quarter (2016-Q3, 2017-Q4, etc) -> TEXT format

         

        installed-base table:

             Number of installed systems: 

             =[Installed Systems]-[Uninstalled Systems]

         

             For cumulative sum of installed base through days

             =SUMX(FILTER(ALLSELECTED('list-days'[Days]);'list-days'[Days]<=MAX ('list-days'[Days]));[Installed Base])

         

        Experiment data:

             Number of run per day

             =COUNT('experiment-data'[Experiment Name])

         

             Run/Installed Base per day:

             =CALCULATE(DIVIDE([Number of run per day];[Installed Base [Day]]]))

         

        If I plot this using DAY as X axes and Y it is ok

        If I change the X axes with year-quarter, what I obtain is TOTAL NUMBER OF RUN/TOTAL NUMBER OF SYSTEMS, but I would like to have the SUM of each NUMBER OF RUN/NUMBER OF SYSTEMS per day

        If I try to add the TOTALQTD to this formula, and plot it QUARTER vs. FORMULA, I obtain the same

         

        Cumulative sum over a quarter:
        =SUMX(FILTER(ALLSELECTED('list-days'[Day]);'list-days'[Day]<=MAX('list-days'[Day]));CALCULATE([Run/Installed Base [Day]]]))

         

        The first row for the first quarter is ok, but the second quarter includes the total of the first quarter + new values and so on