none
Calculating running totals

    Question

  • I have 2 columns in my powerpivot -

    • Fiscal month ID
    • Last Fiscal Month ID (calculated column = [Fiscal month ID - 1] )

    Now, due to complexities I cannot create a date table so cannot use the date functions, but I want to calculate the revenues for the previous fiscal months using the two columns above.

    I was trying something like this, but it doesn't seem to work - can anyone help me on this. Greatly appreciated if you can.


    =SUMX(FILTER(ALL('Sales'), VALUE(Sales[Fiscal Month ID]) = VALUE(Sales[Last Fiscal Month ID] ))

          ,Sales[Total Billed] )

    I can bring back the totals for a single month doing the following - but nothing for the ruuning totals.

    =SUMX(FILTER(ALL('Sales'), VALUE('Sales'[Fiscal Month ID]) = 254 )

          ,'Sales'[Total Billed] )

    Thanks in advance,

     

    Tuesday, May 08, 2012 3:58 PM

Answers

  • Haha,  good man - I should have pointed out that it was DAX, mind you there was some useful VB logic that I picked up for something else.

    Anyway - got it sorted by having to create a date table and assigning [fiscal month last date] to the Sales table and using the groovy date functions!


      CALCULATE(SUM(Sales[Total Billed]),DATESBETWEEN(Calendar[Date],Calendar[Last Fiscal Month - Start Date],Calendar[Last Fiscal Month - End Date] ))

    if anyone is looking for it...

    • Marked as answer by Murtylad Wednesday, May 09, 2012 3:17 PM
    • Edited by Murtylad Wednesday, May 09, 2012 3:19 PM
    Wednesday, May 09, 2012 3:17 PM

All replies

  • hi check out the given link for your reference.

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=6027

    Wednesday, May 09, 2012 3:11 PM
  • Haha,  good man - I should have pointed out that it was DAX, mind you there was some useful VB logic that I picked up for something else.

    Anyway - got it sorted by having to create a date table and assigning [fiscal month last date] to the Sales table and using the groovy date functions!


      CALCULATE(SUM(Sales[Total Billed]),DATESBETWEEN(Calendar[Date],Calendar[Last Fiscal Month - Start Date],Calendar[Last Fiscal Month - End Date] ))

    if anyone is looking for it...

    • Marked as answer by Murtylad Wednesday, May 09, 2012 3:17 PM
    • Edited by Murtylad Wednesday, May 09, 2012 3:19 PM
    Wednesday, May 09, 2012 3:17 PM