2

I'm very new to DAX and PowerPivots. I am using PowerPivot to pull data from an SQL server. The data I'm working with is pretty large and complex but I'm considering a simplified version of it for this question.

Let's say I have 2 columns, one that contains product names and one that contains a date that product was sold. I have a row for each time 1 unit of any product is sold.

Product | Date
Orange  | 08/13/2013
Orange  | 08/13/2013
Orange  | 08/13/2013
Apple   | 08/14/2013
Apple   | 08/16/2013
Orange  | 08/17/2013
Orange  | 08/17/2013

I want to use DAX to get a running count of how much of a product has been sold to date over the entire data set. This is what I would like to end up with.

Product | Date        | Cumulative Sales
Orange  | 08/13/2013  | 1
Orange  | 08/13/2013  | 2
Orange  | 08/13/2013  | 3
Apple   | 08/14/2013  | 1
Apple   | 08/16/2013  | 2
Orange  | 08/17/2013  | 4
Orange  | 08/17/2013  | 5

Any help would be appreciated.

EDIT: One more thing, the data is not necessarily ordered by date. I could potentially order it by date but it would require modification of some other things so my preference would be not to do so if at all possible. There are a lot of other formulas in the sheet I inherited and reordering may break something else.

Predator
  • 469
  • 2
  • 6
  • 20

1 Answers1

2

You can make a calculated measure in PowerPivot to handle this. If you want to get the cumulative sales for all time you can do this: 

    CALCULATE(     SUM( FactSales[SalesAmount] ),
    FILTER(
        ALL( DimDate) ,
        DimDate[Datekey] <= MAX( DimDate[Datekey] )
    )
)

If you want to be able to select certain time period (ex: running total for selected weeks or months) you can do this: 

 CALCULATE( SUM( FactSales[SalesAmount])  ,
                 FILTER(
                    ALLSELECTED( DimDate),
                    DimDate[Datekey] <= MAX( DimDate[Datekey] )
                )
    )

Source: Javier Guillen's blog

mmarie
  • 5,598
  • 1
  • 18
  • 33
  • I posted an answer for you, but I'm confused by your comment about ordering the data breaking formulas in the sheet. At the beginning of your post, you said you were importing data from SQL Server to PowerPivot. I'm not sure what the formulas are that you mentioned. Also, if you are using calculated measures and have a proper date dimension, you should not have to order your data to make it work. – mmarie Aug 19 '13 at 02:37
  • Thanks mmarie. I'm confused by the concept of DimDate. Is that a new table that I need to create? As for the formulas, they're DAX formulas / columns that were added to the workbook by the person who worked on it before me. I'm still new to this so I'm probably not making much sense. I apologize. – Predator Aug 19 '13 at 16:54
  • Yes, you need a date dimension to make it work. You can download one for free from the Windows Azure Data Market: https://datamarket.azure.com/dataset/1542c52d-d466-4094-a801-9ef8fd7c6485 Open PowerPivot, Get External Data, From Data Service, Windows Azure Marketplace. Search for DateStream. You'll need to check/uncheck the boxes at the bottom to choose the calendar you want. – mmarie Aug 19 '13 at 16:58