locked
running total for a period then nothing RRS feed

  • Question

  • I have transactional data. I would like to show the running total from the first transaction date up to the latest transaction date but not beyond. I need to show the running total for dates when no transaction occurred.

    The following formula does what I need except it continues to display the running total for all dates:

    ='FactInternetSales'[Sum of SalesAmount](DATESBETWEEN('DimDate'[FullDateAlternateKey], BLANK(),LASTDATE( 'DimDate'[FullDateAlternateKey])), All('DimDate'))

    Source: http://powerpivot-info.com/post/109-dax-running-totals

    Anyone recommend a way to only show a running total up to the last transaction.

    Saturday, March 12, 2011 9:34 AM

Answers

  • I worked around this by applying an IF to the measure so it reads like 

     

    =If(condition_that_means_dont_display_the runningtotal_beyond_this_time_period = true, BLANK(), running_total_formula)

    • Marked as answer by d_ddd Friday, March 18, 2011 4:13 PM
    Friday, March 18, 2011 11:53 AM

All replies

  • I'm not sure about your intention, but take a look at this post:

    http://sqlblog.com/blogs/alberto_ferrari/archive/2011/03/08/thinking-in-dax-counting-products-in-the-current-status-with-powerpivot.aspx

    It contains many examples that should help you.

    Marco


    Marco Russo
    Sunday, March 13, 2011 9:23 PM
  • I worked around this by applying an IF to the measure so it reads like 

     

    =If(condition_that_means_dont_display_the runningtotal_beyond_this_time_period = true, BLANK(), running_total_formula)

    • Marked as answer by d_ddd Friday, March 18, 2011 4:13 PM
    Friday, March 18, 2011 11:53 AM
  • Hi...I am getting the same error..but i did not get how did you solve the error by using if. For example, if i set the consumption period to 30 days, it will give me the last 30 days sum...but i need a total for everything...i tried using ALL and ALLEXCEPT...but it did not workout..or am i doing something wrong..

    =CALCULATE(SUM('Demand Planning'[QtyIssuedFromStore]),DATESINPERIOD('Activity Date'[FullDate],LASTDATE('Activity Date'[FullDate]),0-MAX('Configurable Values'[Consumption Period]),DAY))


    Words offer the means to meaning, and for those who will listen, the enunciation of truth - V for Vendetta.

    Friday, April 20, 2012 6:58 PM