Forum Discussion

jacksonsong's avatar
jacksonsong
New Member
9 years ago

Cumulative Total

Hi All,   I am trying to create a measure to get the total pax to be the number in the final month when I select a range of months. I.e In this instant, the total pax column sould show "2415" for e...
Need Help
Stharwani's avatar
Stharwani
Advocate II
9 years ago

You could try using

 

Calculate(Sum(totalPax),

Datesbetween(PurchaseDate, min(purchaseDate), max(PurchaseDate)))

  • jacksonsong's avatar
    jacksonsong
    New Member
    9 years ago

    Hi Stharwani,

     

    Thanks for the response. When I put in the measure it was still showing the same numbers in the Sum pf Pax column. While I need it to show I.e "2415" for all purchase month as that would be the total at the end of the month range. Which will lead to the % of total pax measure calulate base on Sum of pax / Total Pax returning a cumulative increase in %.

     

    thanks

    • v-caliao-msft's avatar
      v-caliao-msft
      Microsoft Employee
      9 years ago

      Hi jacksonsong,

       

      In your sample data, the total pax is 6251, why do you need to show 2415 which is only the value for the last month?


      Generally, to create a cumulative total, we can use the DAX below.

      Cumulative Quantity :=

      CALCULATE (

          SUM ( Transactions[Quantity] ),

          FILTER (

              ALL ( 'Date'[Date] ),

              'Date'[Date] <= MAX ( 'Date'[Date] )

          )

      )


      Here are some useful links for you reference.If this is not what you want, please provide us more detail information, so that we can make further analysis.

      http://www.daxpatterns.com/cumulative-total/

      https://javierguillen.wordpress.com/2012/11/28/running-total-techniques-in-dax/

       

      Regards,

      Charlie Liao

      • jacksonsong's avatar
        jacksonsong
        New Member
        9 years ago

        Hi v-caliao-msft,

         

        Thanks for the response. In my data set the total should be 2415. For example in April 2016 there was 1695 which already includes the 949 in mar 2016. Same for every month before. That is why the total of this example is 2415 instead of 6251. I am trying to get the total pax column to show 2415 so the "% of total pax" measure will give me a cumulative % right up to 100% in the final month of May 2016.

         

        Hope this explains.

         

        Thank you!